SQLアンチパターン 第26章 外部キーの誤った使い方から学んだこと
外部キーの誤った使い方から学んだこと
― SQLアンチパターン 第26・27章 ―
SQLアンチパターン第26章では、標準 SQL における外部キーの誤った使い方について扱われています。
、外部キーに関するよくある誤解や実務で起きがちなミスが、
ミニ・アンチパターン集（Tips集）のような形で整理されており、非常に実践的な内容になっています。
読書会を実施した際の、章の概要と議論内容についてまとめていきたいと思います。
外部キーの誤った使い方の概要
第26章で紹介されている外部キーの誤った使い方を、代表的なものに絞って簡潔にまとめます。
1. 参照方向を逆にしてしまう
本来は子テーブルが親テーブルを参照すべきところを、
親テーブルが子テーブルの ID を持ってしまう設計です。
1 対多の関係を正しく表現できず、親テーブルの肥大化や拡張性の低下につながります。
2. 親テーブルを作成する前に子テーブルを定義しようとする
存在しないテーブルや列を参照する外部キーを定義しようとすると、エラーが発生します。
外部キーを使用する場合は、必ず親テーブルを先に作成する必要があります。
3. 親テーブルの主キー・一意キー以外を参照する
外部キーが参照できるのは、親テーブルの PRIMARY KEY または UNIQUE KEY のみです。
一意でない列を参照すると、参照整合性を保証できません。
4. 複合キーを列ごとに分けて外部キー制約を作成する
複合主キーを構成する各列に対して、個別に外部キー制約を定義するのは誤りです。
複合キーは、列の組み合わせとして 1 つの外部キー制約で定義する必要があります。
5. 外部キーの列順を誤る
複合キーを参照する際に、親テーブルと子テーブルで列の順序が一致していないケースです。
列順が異なると、意図した参照関係は成立しません。
6. データ型が一致していない列を外部キーに使用する
数値型のサイズや符号、文字型の長さなどが一致していない場合、
外部キー制約は正しく機能しません。
データ型は完全に一致させる必要があります。
7. 文字コードや照合順序が異なる列を参照する
文字列型の外部キーでは、文字コードや照合順序も一致している必要があります。
MySQL では、この不一致により外部キーが作成できないことがあります。
8. 孤立したデータを許容する設計にしてしまう
外部キーを定義せず、参照整合性をアプリケーション側の制御に任せる設計です。
孤立データが発生しやすく、後から不整合の原因になります。
9. NOT NULL 列に対して ON DELETE SET NULL を指定する
NULL を許可していない列に対して、削除時に NULL を設定するのは論理的に矛盾しています。
制約定義時、または実行時にエラーとなります。
10. 互換性を考慮しない制約やテーブル仕様を使用する
制約名の重複や、互換性のないテーブル仕様は、運用や移植性の問題を引き起こします。
MySQL 固有の注意点
MySQL（特に InnoDB）では、以下の点に注意が必要です。
- 外部キーをサポートしているのは InnoDB のみ
- 外部キー列は インデックス可能なデータ型である必要がある
- BLOB / TEXT / JSON 型は外部キーに使用できない
- パーティションテーブルでは外部キー制約を定義できない
これらの制約を理解していないと、外部キーが意図通りに機能しない原因になります。
読書会で上がった議論について
読書会では、MySQL ではパーティションテーブルに外部キー制約を設定できないという点が話題になりました。
実務でも、いわゆる「モンスターテーブル」のような大きなテーブルを、
年度ごとなどでパーティション分割して運用しているケースは少なくありません。
しかし、その場合、
- パーティションを使うと外部キー制約が使えない
- 参照整合性をデータベース側で保証できなくなる
という問題が発生します。
結果として、
- パーティションされているテーブルに対しては外部キーを諦めざるを得ない
- 新しいテーブルを作成する際にも、「このテーブルはパーティションされているため外部キーを張れない」という制約が生まれる
といった状況になりがちです。
大きなテーブルではパーティションを使いたい一方で、
それによって 外部キーを張れないテーブルが増えていく という点は、
現実的な悩みとして読書会の中でも議論になりました。
個人的な感想
個人的な経験として思い出したのは、前職で関わっていたシステムのことです。
なんでそうなったのか背景はよくわからないのですが、親テーブルと子テーブルがお互いに ID を持つ構造になっているケースがありました。
その結果、
- 親テーブルのカラム数が増え続ける
- どのテーブルがどれを参照しているのか分からなくなる
- さらに、その 2 つのテーブルの ID を両方持たなければならないテーブルが増えていく
といった状態になっていました。
改修のたびにいちいち調査するタスクが発生し、最終的には、
- パフォーマンスが悪すぎて検索スピードが遅い
- 修正や拡張の影響範囲が読めなくなる
- 調査や改修に非常に時間がかかる
といった問題が積み重なり、システムを改善していくこと自体がどんどん難しくなっていった記憶があります。
まとめ
外部キーは、単なる制約ではなく、
データ構造や責務の分離を明確にするための重要な設計要素だと感じました。
一方で、MySQL のパーティション制約など、
理想通りに外部キーを使えない現実があるのも事実です。
そのため、
- 使用しているデータベースの制約
- パーティションを含めたテーブル設計
- 将来の拡張や保守性
これらを踏まえた上で、
どこまでをデータベースに任せ、どこからをアプリケーションで担保するのかを意識的に判断することが重要だと感じました。
外部キーは地味ですが、後になって効いてくる部分だからこそ、
設計段階からしっかり考えていきたいテーマだと思います。
Discussion