外部キーと参照整合性の設計：入門から実務判断まで

リレーショナルデータベースでは、複数のテーブルが「参照」で結びついています。注文テーブルの user_id はユーザーテーブルの誰かを指し、注文明細の order_id はどこかの注文を指します。この「指す・指される」の関係が壊れると、どのユーザーのものか分からない注文や、存在しない注文にぶら下がった明細が生まれます。これを防ぐ仕組みが外部キー（Foreign Key）であり、その背後にある考え方が参照整合性（Referential Integrity）です。

本記事は入門者向けに、外部キーの基本、ON DELETE / ON UPDATE の挙動、貼るべきか外すべきかの判断、実務でハマりやすい落とし穴までを整理します。SQL は PostgreSQL / MySQL を想定していますが、考え方はどの RDBMS でも共通です。

参照整合性とは何か

参照整合性とは、ひとことで言えば「参照先が必ず存在することを保証する」制約です。

• orders.user_id に 42 が入っているなら、users に id = 42 の行が存在しなければならない
• 存在しない値を指す状態（＝孤児レコード / orphan）を作らせない

これをアプリケーションのコードだけで守ろうとすると、必ず抜けが出ます。バグ、並行処理、直接SQLでのデータ修正、バッチ処理——どこか一箇所でチェックを忘れれば整合性は崩れます。外部キー制約は、この保証をデータベース層で強制するための機能です。

基本構文

テーブル作成時に定義する

CREATE TABLE users (
    id    BIGINT PRIMARY KEY,
    name  VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id       BIGINT PRIMARY KEY,
    user_id  BIGINT NOT NULL,
    total    INT NOT NULL,
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) REFERENCES users (id)
);

CONSTRAINT fk_orders_user のように制約に名前を付けるのがおすすめです。名前を付けないとDBが自動採番し、後で制約を削除・変更するときに名前を調べる手間が増えます。

既存テーブルに後から追加する

ALTER TABLE orders
    ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users (id);

この ALTER TABLE は、既存データに孤児レコードが1件でもあると失敗します。エラーが出たら、まず孤児を探します。

SELECT o.id, o.user_id
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

ON DELETE / ON UPDATE：親が消えたら子をどうするか

外部キー設計の肝は、「親が消えたとき、子をどうするか」の指定です。ここを曖昧にしたまま貼ると、後で「消せないレコード」や「意図せず連鎖削除された事故」に悩まされます。

オプション	挙動	使いどころ
RESTRICT / NO ACTION（デフォルト）	子が残っていると親を削除・更新できない	誤削除を防ぎたい。基本はこれ
CASCADE	親を消すと子も自動で消える／更新も伝播	親子が一体で、子単独では意味を持たないとき
SET NULL	子の外部キー列を NULL にする	参照が任意で、外れても子は残ってよいとき（列が NULL 許容である必要あり）
SET DEFAULT	子をデフォルト値にする	用途は限定的

-- 注文が消えたら明細も消す（明細は注文なしでは存在しない）
CREATE TABLE order_items (
    id        BIGINT PRIMARY KEY,
    order_id  BIGINT NOT NULL,
    product   VARCHAR(100) NOT NULL,
    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id) REFERENCES orders (id)
        ON DELETE CASCADE
);

-- ユーザーが消えても投稿は残し、投稿者だけ「不明」にする
CREATE TABLE posts (
    id       BIGINT PRIMARY KEY,
    user_id  BIGINT,               -- NULL 許容にしておく
    body     TEXT NOT NULL,
    CONSTRAINT fk_posts_user
        FOREIGN KEY (user_id) REFERENCES users (id)
        ON DELETE SET NULL
);

CASCADE は便利だが慎重に

ON DELETE CASCADE は連鎖削除です。親を1行消したつもりが、子・孫・ひ孫まで大量に消えることがあります。特に多段のCASCADEが繋がっていると、削除の影響範囲が直感より遥かに広くなります。

安全側に倒すなら、デフォルトは RESTRICT（NO ACTION）にしておき、CASCADE は「子が親と一心同体である」と確信できる関係にだけ付けるのが実務的な指針です。

論理削除との関係

実務では物理削除ではなく deleted_at（論理削除 / ソフトデリート）を使う設計も多くあります。この場合、行はDB上に残り続けるため、外部キー制約は「論理削除された親」を検知しません。DBから見れば親はまだ存在しているからです。「削除済みの親に新しい子を紐付けない」ルールはアプリ側で担保する必要があります。外部キーは「行が物理的に存在するか」しか見ない、と理解しておきましょう。

インデックスとロックの落とし穴

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

多くの人が見落としますが、外部キーを貼ったからといって、子側の列に自動でインデックスが張られるとは限りません。

• PostgreSQL：外部キーを作っても、子側（参照する側）にはインデックスは自動生成されない
• MySQL（InnoDB）：外部キー作成時に、なければ子側に自動でインデックスを作る

参照先の親を削除・更新する際、DBは「その親を指す子がいないか」を子テーブルで探します。子側にインデックスが無いとこの探索がフルスキャンになり、親の削除が非常に遅くなります。PostgreSQL では外部キー列に手動でインデックスを張るのが定石です。

CREATE INDEX idx_orders_user_id ON orders (user_id);

型は完全に一致させる

外部キーの列と参照先の列は、データ型を厳密に揃えます。BIGINT と INT、VARCHAR(50) と VARCHAR(100)、文字コードや照合順序（collation）の違いでも制約作成に失敗したり、インデックスが効かなくなったりします。

実装例：段階的に外部キーを導入する

運用中のテーブルに外部キーを後付けする、現実的な手順です。

-- 1. まず孤児レコードを調査
SELECT COUNT(*) AS orphan_count
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

-- 2. 孤児をどう扱うか決めて修正（例：参照先不明の user_id を NULL に寄せる）
UPDATE orders o
SET user_id = NULL
WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id);

-- 3. インデックスを先に用意
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- 4. 外部キー制約を追加
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE RESTRICT;

PostgreSQL では、検証のロックを軽くするために NOT VALID → VALIDATE CONSTRAINT の二段階も使えます。

ALTER TABLE orders
    ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;

確認方法

制約が意図通り効いているかは、実際に「違反する操作」を試して弾かれることで確認します。

-- 存在しない user_id を挿入 → エラーになれば制約が効いている
INSERT INTO orders (id, user_id, total) VALUES (1, 999999, 1000);

-- 子が残っている親を削除 → RESTRICT なら弾かれる
DELETE FROM users WHERE id = 42;

制約の一覧は、PostgreSQL なら \d orders、MySQL なら SHOW CREATE TABLE orders; で確認できます。

外部キーは貼るべきか

一部の大規模サービスでは、書き込み性能やシャーディングの都合で外部キーをあえて使わず、整合性をアプリ層で担保する設計もあります。ただしこれは「制約のコストと保証を理解した上での意図的なトレードオフ」であって、入門者の初期設計としてはまず外部キーを貼ることをおすすめします。保証がタダで手に入る場面で、わざわざ捨てる理由はありません。

まとめ

• 外部キーは「参照先が必ず存在する」という参照整合性を、DB層で強制する仕組み
• ON DELETE は基本 RESTRICT、一心同体の親子だけ CASCADE、外れてよい参照は SET NULL
• 子側の外部キー列にはインデックスを（特に PostgreSQL は手動で）
• 論理削除・パフォーマンス要件など、外部キーだけでは割り切れない領域があることも知っておく

入門としてはまず「デフォルトで外部キーを貼る」。外すのは、その保証を捨てる理由を説明できるようになってからで十分です。