Kodrill
ログイン無料登録

SQL: テーブル定義とデータベースオブジェクト

OSS-DB Silver

制約・ALTER TABLE・インデックス／ビュー・マテリアライズドビュー・トリガー・シーケンス・スキーマ・テーブルスペース・パーティション／関数・プロシージャ・PL/pgSQL／レプリケーション

問題数
22 問
合格ライン
70%
実行して確認済み
16 問
この分野の要点を読む8 章・約 11 分。実行して確かめたコード例と、試験でよくある引っかけをまとめています

ログインなしで試す

無料の 5 問を、1問ごとに正誤と解説を見ながら解けます。解答は保存されません。

5 問を試す

無料登録して学習を記録する

学習履歴・ダッシュボード・ブックマーク・模試モードが使えます。

無料で登録するログイン

★ 残り 17 問はプレミアム会員限定です

無料プランでは先頭 5 問まで解けます。プレミアムなら全問題・苦手問題の復習・広告非表示が使えます。
料金プランを見る

問題と解説を見る (無料)

登録しなくても、次の5問は問題文と解説をそのまま読めます。

SQL: テーブル定義とデータベースオブジェクトの要点

内容の確認日: 2026-10-29

この分野は出題範囲の S3.1 (SQL コマンド) のうち、テーブルの定義と、ビュー・トリガー・シーケンス・パーティション・関数などのデータベースオブジェクト、レプリケーションの基本を扱います。

オブジェクトどうしには依存関係があり、「依存されているものは削除できない」「CASCADE で依存するものもまとめて削除する」というルールが共通しています。コード例はすべて PostgreSQL 14 で実行した結果です。

制約と ALTER TABLE

制約には PRIMARY KEY (UNIQUE + NOT NULL)・UNIQUE・NOT NULL・CHECK・FOREIGN KEY (REFERENCES) があり、列の既定値は DEFAULT で決めます。UNIQUE では NULL どうしは等しくないので、NULL の行はいくつでも入ります。

外部キーの ON DELETE は、参照先の行を削除したときの動きです。指定しない (NO ACTION) と削除はエラー、CASCADE は参照している行も削除、SET NULL は参照している列を NULL にします。

SQL
CREATE TABLE p (id integer PRIMARY KEY);CREATE TABLE c (pid integer REFERENCES p ON DELETE CASCADE);INSERT INTO p VALUES (1), (2);INSERT INTO c VALUES (1), (1), (2);DELETE FROM p WHERE id = 1;SELECT count(*) FROM c;ALTER TABLE c ADD COLUMN qty integer NOT NULL DEFAULT 0;SELECT pid, qty FROM c;
ターミナル
psql -A -t -P null=NULL -f example.sql
1
2|0

ALTER TABLE では、列の追加・削除 (ADD・DROP COLUMN)・名前の変更 (RENAME)・型の変更 (ALTER COLUMN ... TYPE)・制約の追加 (SET NOT NULL・ADD CONSTRAINT)・所有者の変更 (OWNER TO) ができます。DEFAULT を付けた列の追加では、既存の行にもその値が入ります。SET NOT NULL は既存の行に NULL があるとエラーです。

インデックス

CREATE INDEX 名前 ON テーブル (列) で作り、種類を指定しなければ B-tree です (USING で Hash・GIN・GiST・BRIN などを選べる)。UNIQUE を付けると一意インデックスになり、主キーと一意制約には自動で作られます。lower(email) のような式や、WHERE を付けた一部の行 (部分インデックス) にも作れます。

インデックスを使うかどうかは、プランナが統計情報をもとに決めます。作れば必ず使われるわけではありません。

ビューとマテリアライズドビュー

ビューは問い合わせに名前を付けたもので、1 つのテーブルから列を選ぶだけの単純なビューは自動で更新可能になります。WITH CHECK OPTION を付けると、ビューの条件に合わない行の挿入・更新がエラーになります。

マテリアライズドビューは問い合わせの結果を実際に保存し、元のテーブルを変えても自動では更新されません。REFRESH MATERIALIZED VIEW で作り直します。

SQL
CREATE TABLE orders (id integer, price integer);INSERT INTO orders VALUES (1, 100), (2, 900);CREATE VIEW cheap AS SELECT * FROM orders WHERE price < 500;CREATE MATERIALIZED VIEW order_count AS SELECT count(*) AS n FROM orders;INSERT INTO cheap VALUES (3, 800);SELECT count(*) FROM cheap;SELECT n FROM order_count;REFRESH MATERIALIZED VIEW order_count;SELECT n FROM order_count;
ターミナル
psql -A -t -P null=NULL -f example.sql
1
2
3

WITH CHECK OPTION がないので、条件に合わない行 (800) もビューから元のテーブルに入り、ビューからは見えません。

トリガーとシーケンス

トリガーは、RETURNS trigger の関数を作り、CREATE TRIGGER ... BEFORE (AFTER) INSERT (UPDATE・DELETE・TRUNCATE) ON テーブル FOR EACH ROW (STATEMENT) EXECUTE FUNCTION 関数() で設定します。BEFORE の行トリガーは NEW を書き換えて返すとその内容で書き込まれ、NULL を返すとその行の処理を取りやめます。

シーケンスは番号を発行するオブジェクトで、nextval で進めて値を返し、currval はこのセッションで最後に nextval が返した値 (一度も呼んでいなければエラー)、setval は現在値を設定します。CYCLE を付けると、最大値の次は最小値に戻ります。

SQL
CREATE SEQUENCE s START 10 INCREMENT 5;SELECT nextval('s'), nextval('s'), currval('s');SELECT setval('s', 100), nextval('s');SELECT setval('s', 100, false), nextval('s');
ターミナル
psql -A -t -P null=NULL -f example.sql
10|15|15
100|105
100|100

スキーマ・テーブルスペース・パーティション

スキーマはデータベースの中の名前空間で、スキーマ名.テーブル名 で指定します。中にオブジェクトがあるスキーマは CASCADE なしでは削除できません。テーブルスペースは、データを置くディレクトリに名前を付けたもので、別のディスクにテーブルを置くときに使います (CREATE TABLESPACE 名前 LOCATION 'ディレクトリ'。ディレクトリは前もって作る)。テーブルスペースはクラスタ全体のオブジェクトで、最初から pg_default と pg_global があります。

宣言的パーティションは、PARTITION BY RANGE (範囲)・LIST (値の一覧)・HASH (ハッシュ) で親テーブルを作り、CREATE TABLE ... PARTITION OF 親 FOR VALUES ... で子のテーブルを作ります。範囲の FROM ... TO ... は TO を含みません。どのパーティションにも入らない行はエラーで、DEFAULT パーティションがあればそこに入ります。DETACH PARTITION で切り離し、ATTACH PARTITION で既存のテーブルを付けられます。

SQL
CREATE TABLE sales (d date, amount integer) PARTITION BY RANGE (d);CREATE TABLE sales_q1 PARTITION OF sales  FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');CREATE TABLE sales_other PARTITION OF sales DEFAULT;INSERT INTO sales VALUES ('2026-03-31', 1), ('2026-04-01', 2);SELECT tableoid::regclass, amount FROM sales ORDER BY amount;
ターミナル
psql -A -t -P null=NULL -f example.sql
sales_q1|1
sales_other|2

関数・プロシージャ・PL/pgSQL

関数は CREATE FUNCTION 名前(引数) RETURNS 型 LANGUAGE 言語 AS '本体' で作り、式の中で呼び出します。本体は文字列なので、ドル引用符 ($$...$$) で囲むと中の単一引用符をそのまま書けます。PL/pgSQL では DECLARE で変数を宣言し、BEGIN 〜 END に処理を書きます。同じ名前でも引数の型が違えば別の関数として作れます。

プロシージャ (CREATE PROCEDURE) は値を返さず、CALL で呼び出し、中で COMMIT・ROLLBACK を実行できます。関数の中ではトランザクションを確定できません。

SQL
CREATE FUNCTION add(a integer, b integer) RETURNS integer  LANGUAGE sql AS 'SELECT a + b';CREATE TABLE log (msg text);CREATE PROCEDURE write_log() LANGUAGE plpgsql AS $$BEGIN  INSERT INTO log VALUES ('a');  COMMIT;  INSERT INTO log VALUES ('b');  ROLLBACK;END;$$;SELECT add(2, 3);CALL write_log();SELECT msg FROM log;
ターミナル
psql -A -t -P null=NULL -f example.sql
5
a

レプリケーション

・ストリーミングレプリケーション (物理): プライマリの WAL をスタンバイに送り、クラスタ全体を複製する。スタンバイは読み取り専用 (hot_standby で問い合わせ可能)。既定は非同期で、synchronous_standby_names を設定すると同期になる。障害時は pg_ctl promote でスタンバイを昇格させる ・ロジカルレプリケーション: 送る側で CREATE PUBLICATION、受ける側で CREATE SUBSCRIPTION を作り、テーブル単位で行の変更を複製する。送る側は wal_level = logical。DDL は複製されず、受ける側も書き込める。メジャーバージョンが違っても使える

試験でよくある引っかけ

  • UNIQUE と NULL

    NULL どうしは重複とみなされないので、UNIQUE の列に NULL を複数入れられます。

  • 依存されているオブジェクトの削除

    外部キーで参照されるテーブルや、中身のあるスキーマは、CASCADE なしでは削除できません。

  • 更新可能ビューの条件

    WITH CHECK OPTION がないと、ビューの条件に合わない行も元のテーブルに入ります。

  • マテリアライズドビューは自動更新されない

    REFRESH MATERIALIZED VIEW を実行するまで、作成時の結果のままです。

  • currval は nextval の後

    そのセッションで nextval を呼ぶ前に currval を呼ぶとエラーです。

  • 範囲パーティションの TO

    TO の値は含まれません。範囲外の行は DEFAULT パーティションがなければエラーです。

  • プロシージャと関数

    トランザクションを制御できるのはプロシージャで、CALL で呼びます。

  • ロジカルレプリケーションと DDL

    テーブルの定義の変更は複製されないので、受ける側にも同じ定義を用意します。

問題で確認する

この分野の問題を、解説つきで読めます (登録不要)。

出典・参考

コード例は、PostgreSQL 14 で実際に実行して結果を確認しています。