はじめに
実務では「正規化すれば健全、外部キーで整合性が守れる」と学びます。
たしかに 第3正規形＋外部キー までは多くの現場で安定します。
一方で、やりすぎた正規化 と 外部キーだらけ の設計で、運用が詰むことも…。
基本から実務の失敗談 までをお話したいと思います。
基本：第3正規形までと外部キー
「ユーザー」と「注文」を分け、外部キーで整合性を担保する—ここは鉄板だと思います！
-- users
CREATE TABLE users (
user_id BIGINT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
-- orders
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
order_date DATE NOT NULL,
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
- 存在しないユーザーに注文がぶら下がらない
- 削除時の挙動（CASCADE / RESTRICT / SET NULL）が選べる
つまり第3正規形までは、外部キーの「守り」がきれいに効く形。
失敗談1：住所を過正規化してJOIN地獄
「都道府県」「市区町村」「町域」をすべて独立マスタ化。
参照は常に3〜4段JOIN。
検索やCSV出力が激重になり、SQLが読めず属人化しました。
SELECT u.name, p.name AS pref, c.name AS city, a.street
FROM users u
JOIN addresses a ON a.address_id = u.address_id
JOIN cities c ON c.city_id = a.city_id
JOIN prefectures p ON p.pref_id = c.pref_id;
- 常にJOINが必要な属性は、非正規化して冗長に持つ選択肢を検討
- 分析基盤では厳密に、アプリDBは実用優先に、という分離も有効
失敗談2：外部キーが多すぎて削除も更新も進まない
参照元が10テーブル以上ぶら下がる親テーブルを RESTRICT（子が残ると削除禁止）で固めた結果、
「1件の削除に半日」「バッチが毎晩コケる」状態に…。
ALTER TABLE child
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id) REFERENCES parent(parent_id) ON DELETE RESTRICT;
- 論理削除（deleted_at や is_active） に切り替える
- 物理削除は階層ごとに順序制御する or CASCADE を限定利用
- 「絶対に消えちゃダメ」以外は SET NULL / SET DEFAULT も検討
失敗談3：CASCADEで意図せぬ大規模削除
検証環境で「親1件消したら関連が全部消えてスカスカ」に…。
テストデータで見落とし、本番で冷や汗が止まらない状態💦
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(user_id)
ON DELETE CASCADE;
- CASCADE は 葉側 に限定する
- 本番前に参照グラフをレビュー（ER 図で可視化）
- 重要データは RESTRICT + アプリ側手続き削除
失敗談4：トランザクションが重く、デットロック地獄
外部キー検査＋多段 JOIN＋大量更新でデットロックが発生し、ピーク時にタイムアウト頻発。
- 書き込みは 小分けバッチ化
- 更新単位を小さくし、整合性は DB に任せる
- 読み取り系は レプリカやマテビュー で逃がす
「第3正規形まで」と「それ以降」の実務的境界
-
第3正規形まで
- 冗長性削減と整合性確保のバランスがよい
-
それ以降（BCNF, 第4, 第5）
- 冗長性はさらに減るが、外部キーと JOIN が急増
- 分析 DB なら有効、業務 DB では過剰になりがち
-- 第4正規形寄りの分解（社員×スキル）
CREATE TABLE employee_skills (
emp_id BIGINT NOT NULL,
skill_id BIGINT NOT NULL,
PRIMARY KEY (emp_id, skill_id),
FOREIGN KEY (emp_id) REFERENCES employees(emp_id),
FOREIGN KEY (skill_id) REFERENCES skills(skill_id)
);
外部キー設計の実務パターン集
外部キー設計のパターンとチェックリストを、自分の覚書もかねてまとめてみました。
多分きっと…実務にあてはめられるかと思います！
1) 重要データは強め（RESTRICT/NO ACTION）
FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE RESTRICT;
売上・請求などは 削除禁止 を基本に。
2) ログ・一時データは緩め（CASCADE/SET NULL）
FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE;
親が消えたら不要なものは掃除を自動化。
3) 参照頻度が高いマスタは冗長保持もあり
-- 外部キーで ID を持ちつつ、名前も冗長保持する例
supplier_id BIGINT NOT NULL,
supplier_name VARCHAR(100) NOT NULL
JOIN 削減で画面や帳票が軽くなる。変更頻度が低ければ有効。
4) FK は“全部張らない”
外部キーは 守りの装置だが、運用を止める装置にもなり得ます。
クリティカルな箇所だけ張り、補助はアプリ検証に任せることも必要です。
運用確認チェックリスト
- 参照チェーンを 3 段超えさせない（超えるなら冗長化 / ビュー）
- CASCADE は到達範囲をレビュー
- 物理削除は例外、基本は論理削除
- 大量更新は夜間バッチ＋小分け＋再実行設計
- 参照整合性は DB、検索性能は冗長化 / 派生テーブルで担保
- ER 図や依存関係を自動生成して可視化
まとめ
- 第3正規形＋必要最低限の外部キー が業務 DB の現実解
- それ以上の正規化は分析用なら有効だが、業務 DB では過剰
- 外部キーは「守りの最後尾」。
張りどころを見極め、冗長化・論理削除・派生テーブルと組み合わせるのが実務的最適化