Menu

PostgreSQL の外部キーと ON DELETE CASCADE

外部キーを使うと、列は別のテーブルに存在する値しか受け付けなくなります。REFERENCES、ON DELETE CASCADE、SET NULL、RESTRICT、既存のテーブルへのキーの追加、インデックスの作成を扱います。

このページのコードはエディタで実行できます。編集してすぐに結果を確認できます。

外部キーは、列を別のテーブルの主キーまたは一意の列に結びつけます。PostgreSQL は、参照先に一致する行がない値をすべて拒否します。column type REFERENCES other_table (column) と書きます。親が削除されたときに子の行をどうするかは、ON DELETE CASCADE や ON DELETE SET NULL で指定します。

参照される列には PRIMARY KEY か UNIQUE 制約が必要です。列を書かずに REFERENCES customers とすると、PostgreSQL は customers の主キーを使います。

違反するとどうなるか

存在しない顧客の注文は拒否されます。

エラーには制約名 orders_customer_id_fkey が表示されます。これは名前のない外部キーに PostgreSQL が付ける名前です。まだ注文がある顧客を削除しようとしても、キーで別の動作を指定していない限り、同じように失敗します。

ON DELETE のオプション

オプション親の行が削除されたとき
NO ACTION (デフォルト)子の行がある間は削除が失敗する
RESTRICT同じだが、チェックをトランザクションの最後まで遅らせられない
CASCADE子の行も削除される
SET NULL子の外部キーの列が NULL になる
SET DEFAULT子の列がデフォルト値になる

ON UPDATE にも同じオプションがあります。親のキーの値が変わったときに適用されますが、生成された id ではめったに起きません。

CASCADE は、注文の明細のように親がないと意味のない行に向いています。

残るのは chair だけです。カスケードはさらに先のキーにも及ぶので、1 回の削除で複数のテーブルの行が消えることがあります。

SET NULL は子の行を残したまま、親との関係を切ります。

Ada は残り、team_id は NULL になります。PostgreSQL 15 からは、ON DELETE SET NULL (column) で複数列のキーの一部の列だけを空にできます。

既存のテーブルに外部キーを追加する

ALTER TABLE orders
    ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;

これは既存のすべての行をチェックし、親のない行が 1 つでもあれば失敗します。大きなテーブルでは、新しい行だけをチェックする NOT VALID を付けてキーを追加します。そのあと ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey; を実行すると、書き込みをブロックせずに既存の行をチェックできます。既存のキーの ON DELETE の動作を変えるには、1 つの ALTER TABLE の中で制約を削除して追加し直します。

外部キーの列にインデックスを作る

PostgreSQL がインデックスを持つのは参照される側で、参照する側の列ではありません。orders.customer_id にインデックスがないと、customers から削除するたびに子の行を探すため orders 全体をスキャンします。1 人の顧客の注文を取得するときも同じです。

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

テーブルの外部キーの一覧は pg_constraint を検索して表示します。

これらのキーを使った結合は JOIN のページ で扱います。遅延可能なキーを含む詳しいルールは、マニュアルの 制約の章 にあります。

よくある質問

PostgreSQL は外部キーにインデックスを作りますか?

参照する側の列には作りません。参照される側の列は主キーか一意である必要があるので、すでにインデックスがあります。親テーブルとの JOIN や親テーブルからの削除でこの列が検索されるので、外部キーの列には自分でインデックスを作ってください。

PostgreSQL で外部キーを削除するには?

ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey; を実行します。名前を付けていない外部キーは table_column_fkey という名前になります。正確な名前は psql の \d orders で確認できます。

PostgreSQL の外部キーの列は NULL にできますか?

できます。外部キーの列の NULL はチェックされないので、その行には親が要りません。すべての行に親が必要なら NOT NULL を付けてください。

外部キーで同じテーブルを参照できますか?

できます。manager_id int REFERENCES employees (id) のような列は、同じテーブルの別の行を指します。組織図やコメントのスレッドのようなツリー構造は、この方法で保存します。

Coddyのプログラミング言語のイラスト

Coddyでコードを学ぼう

始める