外部キーは、列を別のテーブルの主キーまたは一意の列に結びつけます。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) のような列は、同じテーブルの別の行を指します。組織図やコメントのスレッドのようなツリー構造は、この方法で保存します。