外部キー制約(foreign key constraint)は、ある表のカラムに入っている値が、別の表に実在するレコードを指していることを DB(データベース)に保証させる仕組みです。PostgreSQL の定義では、1 つのカラムまたは複数のカラムの組の値が、別の表のいずれかのレコードに現れる値と一致する必要があります。この保証が保たれている状態を参照整合性(referential integrity)と呼びます。

注文の一覧に存在しない会員の ID が混ざると、会員別の集計と注文表全体の合計が食い違います。その ID が INSERT の時に入ったのか、会員を消した時に生まれたのかも、後からは辿れません。

例えば、会員(members)と注文(orders)で、会員 42 を消した時の違いは以下のようになります。

左は外部キーの無い表で、会員 42 を DELETE すると成功し、42 を指していた 2 件の注文が参照先の無い孤児レコードとして残る。右は外部キーのある表で、同じ DELETE がエラーで止まり、会員 42 は消えない

外部キーの有無で、会員 42 の DELETE の結果が分かれる

上記の図の左右の表で、参照される members を親、参照する orders を子と呼びます。子の member_id が外部キーで、制約が無い左では親だけが消えます。


本記事では、1 つの DB の中で表と表を結ぶ外部キー制約を説明します。孤児レコードが生まれる経路から始めて、次の疑問に答えます。

  • 外部キーが無いと、参照先の無いレコードはどんな経路で生まれるのか。
  • アプリケーションで親の存在を確認するだけでは、なぜ足りないのか。
  • 確認と書き込みの間に親が消えた時、外部キー制約はどう止めるのか。
  • 親のレコードを消す時、子のレコードをどう扱うのか。
  • 外部キーを張らない場合、誰が参照整合性を守るのか。

サービスをまたいだ参照には、「外部キーを張らない場合に整合性を守る担当」の節でだけ触れます。以降の SQL は PostgreSQL の構文で書きます。


外部キーが無い時に生まれる孤児レコード

制約は子の表に書きます。参照先のカラムは、値から 1 件に決まる(重複しない)ことを DB が保証している必要があります。PostgreSQL でこの保証に当たるのは、主キー(その表でレコードを一意に決めるカラム)・一意制約・一意索引(部分索引を除く)のどれかです。

索引は、カラムの値から該当するレコードの位置を引ける補助の構造です。参照する値が参照先で一意に特定できないと、この CREATE TABLE 自体が失敗します。

CREATE TABLE members (
    id   BIGINT PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id        BIGINT PRIMARY KEY,
    member_id BIGINT NOT NULL REFERENCES members (id),
    amount    INT NOT NULL
);

制約を書かなかった場合、member_id はただの整数のカラムになります。DB は、その値が members の誰かを指しているかどうかを見ません。参照先の無い子のレコードは孤児レコードと呼ばれ、次の 2 つの経路で生まれます。

  %%{init: {"theme":"base","themeVariables":{"primaryColor":"#eaf1f5","primaryTextColor":"#28343b","primaryBorderColor":"#9cabb5","lineColor":"#596b76","textColor":"#28343b","actorBkg":"#eaf1f5","actorBorder":"#9cabb5","actorTextColor":"#28343b","actorLineColor":"#9cabb5","signalColor":"#596b76","signalTextColor":"#28343b","noteBkgColor":"#f7f1e5","noteBorderColor":"#c5b99b","noteTextColor":"#403c32","activationBkgColor":"#edf3ef","activationBorderColor":"#9aafa1"}}}%%
flowchart TB
    A["存在しない ID で INSERT<br/>member_id = 999"] --> O["孤児レコード<br/>参照先の無い注文"]
    B["親を DELETE<br/>会員 42 を削除"] --> O
    O --> C["INNER JOIN で消える"]
    O --> D["会員別の集計に入らない"]
    O --> E["注文から会員名を引けない"]
    classDef default fill:#eaf1f5,stroke:#9cabb5,color:#28343b;

どちらの経路でも、INSERT や DELETE はエラーを返しません。制約が無いので、DB にとっては正常な書き込みです。孤児が生まれた瞬間には何も起きず、後から読む時に影響が出ます。

-- 外部キー制約を張っていない orders の場合。members に id = 999 のレコードは無い
INSERT INTO orders (id, member_id, amount) VALUES (1001, 999, 3000);

このレコードを含む注文表に会員名を付けて一覧する問い合わせを考えます。orders と members を INNER JOIN で繋ぐと、対応する会員のレコードが無い注文 1001 は結果から落ちます。orders を単独で合計した売上とは、3,000 円の差が出ます。

この差が読み取りの誤りではない点が、調査を長引かせる原因になります。どちらの問い合わせも書いた通りに動いており、JOIN の結果が正しい以上、DB は矛盾を報告しません。孤児レコードを検出するには、参照先が存在しないレコードを検索する必要があります。


アプリケーションでの存在確認が届かない範囲

外部キー制約を張らずに、注文を作る処理の中で会員の存在を確認する方法もあります。注文 API のコードに SELECT を 1 本足せば済む、という判断です。この方法が守れるのは、その処理を通った書き込みだけになります。表を更新する経路が 1 本だとは限りません。

例えば、同じ orders を更新し得る経路には次のようなものがあります。

  %%{init: {"theme":"base","themeVariables":{"primaryColor":"#eaf1f5","primaryTextColor":"#28343b","primaryBorderColor":"#9cabb5","lineColor":"#596b76","textColor":"#28343b","actorBkg":"#eaf1f5","actorBorder":"#9cabb5","actorTextColor":"#28343b","actorLineColor":"#9cabb5","signalColor":"#596b76","signalTextColor":"#28343b","noteBkgColor":"#f7f1e5","noteBorderColor":"#c5b99b","noteTextColor":"#403c32","activationBkgColor":"#edf3ef","activationBorderColor":"#9aafa1"}}}%%
flowchart LR
    A["注文 API<br/>存在確認あり"] --> T[(orders)]
    B["夜間バッチ<br/>他社データの取り込み"] --> T
    C["管理者が手で流す SQL"] --> T
    D["Migration<br/>表の定義を変える一括処理"] --> T
    E["障害復旧の際の<br/>データ修正"] --> T
    classDef default fill:#eaf1f5,stroke:#9cabb5,color:#28343b;

上記の図で存在確認を行うのは注文 API だけです。経路が増えるたびに、同じ検査を書き足す必要があります。書き足し忘れても構文の誤りにはならないので、その経路からの書き込みは DB に止められません。

外部キー制約は、この検査を DB に 1 つ置きます。どの経路から来た INSERT でも同じ検査が掛かり、通らない書き込みはエラーで止まります。注文 API だけでなく、バッチや手動 SQL からの書き込みにも適用されます。


存在の確認と書き込みの間に起きる削除

アプリケーションでの存在確認には、経路の数とは別の弱点があります。SELECT で親のレコードを確認してから INSERT するまでの間に、別のトランザクションが親を消せる点です。経路を 1 本に絞り込んでも、この弱点は残ります。

トランザクションは、まとめて成功か失敗かを決める書き込みのひとかたまりで、COMMIT で確定します。以下は、2 つのトランザクションが重なった時の順序です。

  %%{init: {"theme":"base","themeVariables":{"primaryColor":"#eaf1f5","primaryTextColor":"#28343b","primaryBorderColor":"#9cabb5","lineColor":"#596b76","textColor":"#28343b","actorBkg":"#eaf1f5","actorBorder":"#9cabb5","actorTextColor":"#28343b","actorLineColor":"#9cabb5","signalColor":"#596b76","signalTextColor":"#28343b","noteBkgColor":"#f7f1e5","noteBorderColor":"#c5b99b","noteTextColor":"#403c32","activationBkgColor":"#edf3ef","activationBorderColor":"#9aafa1"}}}%%
sequenceDiagram
    participant A as 注文 API
    participant DB as DB
    participant B as 退会処理
    A->>DB: SELECT id FROM members<br/>WHERE id = 42
    DB-->>A: 1 件ある
    B->>DB: DELETE FROM members<br/>WHERE id = 42
    B->>DB: COMMIT
    A->>DB: INSERT INTO orders<br/>(member_id = 42)
    A->>DB: COMMIT
    Note over A,B: 確認は成功し<br/>結果は孤児レコード

上記の図の注文 API は、自分の見た結果に従って正しく動いています。確認した時点では会員 42 が実在し、INSERT を出す時点では実在しません。

競合した時にどう振る舞うかは、分離レベル(同時に走るトランザクションどうしが互いの変更をどこまで見るかの設定)・ロックの取り方・DBMS(Database Management System)の実装方式で変わります。

これらを工夫して競合を防げても、「orders.member_id は必ず members.id を指す」という規則を守り続ける責任は、注文を INSERT する処理にも会員を DELETE する処理にも残ります。分離レベルは Transaction Isolation のノートで扱っています。

外部キー制約を書けば、確認と書き込みを DB が 1 つの操作として扱います。PostgreSQL 17.10 で外部キー制約を張った表に、上記の図の 2 本を流しました。図と違い、退会処理は DELETE から COMMIT までを 3 秒空け、その途中で注文 API が INSERT を出しています。

-- 注文 API 側の psql(抜粋)
INSERT INTO orders (id, member_id, amount) VALUES (1001, 42, 3000);
ERROR:  insert or update on table "orders" violates foreign key constraint "orders_member_id_fkey"
DETAIL:  Key (member_id)=(42) is not present in table "members".

外部キーの検査は会員 42 のレコードにロックを取ろうとするので、INSERT は退会処理の COMMIT まで待たされ(約 2.5 秒)、会員が消えたと分かった時点でエラーになりました。注文 1001 は入らず、孤児は生まれていません。COMMIT が INSERT より先なら、待たずに同じエラーで止まります。


制約を書いても検査されないレコード

外部キー制約を書いても、全てのレコードが検査されるとは限りません。まず、制約が有効になっていることが必要です。

SQLite のドキュメントには「Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.」(外部キー制約は既定で無効で、接続ごとに有効にする必要がある)とあります。

制約を後から張る場合は、検査の範囲に注意が必要です。PostgreSQL で NOT VALID を付けると、制約を足す前からあるレコードの一括検査を省けます。以降の INSERT と UPDATE には制約が掛かり、既存のレコードは後から VALIDATE CONSTRAINT で検証できます。

参照のカラムが NULL のレコードも、外部キー制約に違反しません。PostgreSQL には「Normally, a referencing row need not satisfy the foreign key constraint if any of its referencing columns are null.」(参照する側のカラムのいずれかが NULL であれば、通常そのレコードは外部キー制約を満たす必要が無い)と書かれています。

member_id を NULL 可にすると、会員を指さない注文が制約を通ります。上の DDL では NOT NULL を付けているので、そのレコードは入りません。


ON DELETE の指定と親子の所有関係

外部キー制約を張ると、親のレコードを消す操作の意味を決める必要が出ます。ON DELETE に何を書くかで、削除が子にどう及ぶのかが変わります。制約の検査は通常は文ごとに行われ、DEFERRABLE を付けた制約では COMMIT まで遅らせられます。PostgreSQL が用意している指定で、会員 42 を消した後に残るものは以下の通りです。

ON DELETE の指定ごとの 4 列。NO ACTION と RESTRICT は会員 42 が残り、注文は member_id = 42 のまま会員を指し、DELETE はエラーになる。CASCADE は会員も注文も点線で消える。SET NULL は会員が消え、注文は member_id = NULL で残る。SET DEFAULT は会員が消え、注文は member_id = 既定値で残る

ON DELETE の指定ごとの、親を消した後の子

CASCADE の連鎖は、子の子にも続きます。SET NULL は、参照のカラムが NOT NULL ならその DELETE が失敗します。SET DEFAULT は、既定値が NULL 以外なら、その値を持つ親のレコードが必要です。


既定の NO ACTION と RESTRICT は、どちらも子が残っていれば親を消せません。違いは、NO ACTION だけが DEFERRABLE を付けた制約で検査を遅らせられることです。ただし既定は NOT DEFERRABLE なので、上の DDL のままでは検査を遅らせられません。

検査を遅らせられると、親と子をまとめて差し替える処理を書けます。例えば、親のレコードをいったん消して同じ ID で入れ直す処理は、途中で一時的に参照先が欠けても、COMMIT の時点でそろっていれば違反になりません。RESTRICT はこうした操作も拒否します。利用できる指定と検査のタイミングは DBMS で違います。

どれを選ぶかは、性能や書きやすさではなく、子のレコードが親なしで意味を持つかどうかで決まります。以下が判断の分かれ目です。

  %%{init: {"theme":"base","themeVariables":{"primaryColor":"#eaf1f5","primaryTextColor":"#28343b","primaryBorderColor":"#9cabb5","lineColor":"#596b76","textColor":"#28343b","actorBkg":"#eaf1f5","actorBorder":"#9cabb5","actorTextColor":"#28343b","actorLineColor":"#9cabb5","signalColor":"#596b76","signalTextColor":"#28343b","noteBkgColor":"#f7f1e5","noteBorderColor":"#c5b99b","noteTextColor":"#403c32","activationBkgColor":"#edf3ef","activationBorderColor":"#9aafa1"}}}%%
flowchart TB
    Q{"親が消えた後、子のレコードは<br/>単独で意味を持つか"}
    Q -->|"持たない<br/>注文と注文明細"| C["CASCADE<br/>親の一部として消す"]
    Q -->|"持つ<br/>会員と注文"| R["NO ACTION / RESTRICT<br/>消させない"]
    Q -->|"持つが参照は切って良い<br/>担当者と案件"| S["SET NULL<br/>参照だけ外す"]
    classDef default fill:#eaf1f5,stroke:#9cabb5,color:#28343b;

上記の図の注文明細は、注文 1 件に含まれる商品ごとのレコードです。注文が消えれば残す理由が無く、所有している側が消える時に一緒に消える関係なので CASCADE が合います。

会員と注文は違い、退会しても過去の売上の記録として残す必要があります。案件から担当者への参照のように、相手が居なくなってもレコードそのものは残したい関係では SET NULL が合います。

CASCADE を「親を消す時に子を消す手間が省ける指定」として選ぶと、子が親なしで意味を持つかという判断を飛ばしてしまいます。連鎖は子の子にも続くので、会員を 1 件消した結果として、注文と注文明細がまとめて消える構成にもなります。レコードを残したい要求は、Soft Delete のノートで扱う設計課題です。


外部キーを張らない場合に整合性を守る担当

外部キー制約を置かない構成もあります。代表的なのは次の 3 つです。

  • 親と子が別のサービスの DB にあり、参照が DB の外をまたぐ
  • シャーディング(1 つの表を複数のサーバーに分けて置く構成)で親と子が別のノードにある
  • 履歴や監査ログのように、現在の親との参照整合性より、記録した時点の情報をそのまま残すことを優先する

前の 2 つは、制約を置ける場所に親が居ない形です。3 つ目は親が同じ DB に居るので事情が違います。追記しかしないから張れないのではなく、現在の会員との関係を保つ必要が無く、その時点の記録として独立して残すことに意味があるからです。RESTRICT で親の削除を止めるのも CASCADE で消すのも、この要求には合いません。

3 つ目では、SET NULL で参照だけ外す選択も残っています。参照のカラムを NOT NULL にできないことと、消えた相手が誰だったかを別のカラムに写しておく必要が出ることが、その代償です。記録として残すカラムを別に持てるなら、外部キー制約を保ったままレコードを残せます。

制約を外した場合、参照整合性を守る担当は DB の外に移ります。移し先は、代表的には次の 3 つです。

  • 親と子を変更する経路を 1 か所に集め、作成・更新・削除の全部で同じ参照整合性の規約を守る
  • 孤児を定期的に検出し、業務の判断で直すか消すかを決める
  • 孤児が居る前提で読み、表示を組み立てる処理で欠けた参照を扱う

1 つ目は、注文を作る API を 1 本にするだけでは足りません。会員を消す処理が別にある限り、その処理でも、参照する注文がないかを確認する必要があります。2 つ目の検出は、LEFT JOIN で書けます。この結合は対応する会員が無くても注文のレコードを残し、会員のカラムを NULL にします。

SELECT o.id, o.member_id
FROM orders o
LEFT JOIN members m ON m.id = o.member_id
WHERE o.member_id IS NOT NULL
  AND m.id IS NULL;

m.id IS NULL が孤児の条件になるのは、members.id が NOT NULL の主キーだからです。o.member_id IS NOT NULL を足しているのは、初めから誰も参照していないレコードと、参照先が消えたレコードを混ぜないためです。2 つは業務上の扱いが違います。

検出は、孤児が生まれることを止めません。見付かった時点で、その注文をどう扱うのかという業務の判断が必要です。3 つ目の「居る前提で読む」は、その判断を読み取りの処理に埋め込む方法で、会員名の欄に「退会済み」と出すような扱いになります。

外部キーを張らない設計は、条件がそろえば妥当な選択です。決めておくことは、参照先の存在を誰がいつ確認するのか、確認できなかったレコードをどう扱うのかの 2 つになります。この 2 つを決めないまま制約を外すと、担当が誰にも割り当てられていない状態になります。


利点

  • 制約が有効である限り、どの経路から来た書き込みでも参照先の無いレコードが入らない
  • 親の削除と子の追加が競合しても、参照整合性の維持を DB に任せられる
  • 親を消した時の扱いが表の定義に宣言として残り、経路ごとの実装に散らない
  • 表と表の関係が定義から読み取れ、後から構造を辿れる

欠点

外部キー制約を使うと、DB での検査処理やデータを投入する順序に、次の制約があります。

  • 子への書き込みのたびに親の存在を確認するので、書き込みの負担が増える
  • 親の削除や更新のたびに子を探すので、子のカラムに索引が無いと表の走査になる
  • 表をまたぐ検査なので、親と子が別のノードに分かれる構成では張りにくい
  • 一括での投入や DB の移行で、親から先に入れるという順序の縛りが出る
  • CASCADE の連鎖が、設計時に想定した範囲を超えて広がることがある

欠点の 2 つ目に挙げた子のカラムの索引は、DBMS によって扱いが違います。PostgreSQL のドキュメントには「the declaration of a foreign key constraint does not automatically create an index on the referencing columns」(外部キー制約の宣言は、参照するカラムに索引を自動では作らない)とあります。

DBMS によっては自動で作るものもあります。例えば MySQL の InnoDB は、外部キーの検査で表を走査しないように、参照する側に使える索引が無ければ自動で作ります。

索引の有無で親の削除がどう変わるのかを以下の図に示します。

  %%{init: {"theme":"base","themeVariables":{"primaryColor":"#eaf1f5","primaryTextColor":"#28343b","primaryBorderColor":"#9cabb5","lineColor":"#596b76","textColor":"#28343b","actorBkg":"#eaf1f5","actorBorder":"#9cabb5","actorTextColor":"#28343b","actorLineColor":"#9cabb5","signalColor":"#596b76","signalTextColor":"#28343b","noteBkgColor":"#f7f1e5","noteBorderColor":"#c5b99b","noteTextColor":"#403c32","activationBkgColor":"#edf3ef","activationBorderColor":"#9aafa1"}}}%%
flowchart LR
    D["DELETE FROM members<br/>WHERE id = 42"] --> Q{"orders.member_id に<br/>索引があるか"}
    Q -->|"ある"| I["索引を引いて<br/>該当する注文を探せる"]
    Q -->|"無い"| S["orders を端から走査して<br/>member_id = 42 を探す"]
    classDef default fill:#eaf1f5,stroke:#9cabb5,color:#28343b;

上記の図の索引が無い経路では、子の表が大きくなるほど親を 1 件消す操作が重くなります。索引の構造は B-Tree のノートで扱っています。

PostgreSQL 17.11 で、この差を測りました。members は 10 万件で、orders の member_id には 1〜50,000 を一様に入れます。注文を持たない会員を 1 件 DELETE し、外部キーの検査を含む実行時間を EXPLAIN ANALYZE で読みました。BEGIN と ROLLBACK で挟んで表を変えずに 21 回繰り返し、中央値を取っています。表は全て共有バッファに載った状態です。

# 抜粋。全体は lab/fk_delete.py
for i in range(REPEAT + 1):
    out = psql("BEGIN;\n"
               f"EXPLAIN (ANALYZE, COSTS OFF) DELETE FROM members WHERE id = {60_000 + i};\n"
               "ROLLBACK;\n")

全体は lab/fk_delete.py にあります。結果は次の通りです。

子の表の件数を横軸、親 1 件の DELETE の時間を縦軸に取った両対数のグラフ。索引が無いと時間は件数にほぼ比例して 400 万件で約 240 ms に伸び、索引があると件数によらず 1 ms 未満に留まる。

子の件数と、親 1 件の DELETE にかかる時間


測定値(抜粋)
child_rows  index  delete_ms  fk_check_ms   scan of orders in the FK lookup
     10000  no         0.800        0.635   Seq Scan
     10000  yes        0.430        0.257   Index Scan using orders_member_id_idx
   1000000  no        61.545       61.222   Seq Scan
   1000000  yes        0.684        0.433   Bitmap Heap Scan
   4000000  no       238.153      237.964   Seq Scan
   4000000  yes        0.768        0.527   Bitmap Heap Scan

上記の図の通り、索引が無い(no index on orders.member_id)と親 1 件の削除にかかる時間は子の件数にほぼ比例し、400 万件では約 240 ms でした。時間のほとんどは外部キーの検査(測定値の fk_check_ms)です。索引があれば、どの件数でも 1 ms 未満でした。

欠点の 3 つ目に挙げた、親と子が別のノードに分かれる構成についても、分散を前提に作られた DBMS には、CockroachDB のように外部キー制約を支援するものがあります。