はじめに

実務では「正規化すれば健全、外部キーで整合性が守れる」と学びます。
たしかに 第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 では過剰
外部キーは「守りの最後尾」。
張りどころを見極め、冗長化・論理削除・派生テーブルと組み合わせるのが実務的最適化