なぜ MySQL で外部キー制約を設定しないのか
はじめに
エンジニアとしてのカミングアウトになるが、これまで業務の中で外部キー制約を設定したことが1度もない。
業務で扱うテーブルは目的に応じて（ある程度）適切に正規化され、SQLアンチパターン に記載されているようなアンチパターンにも注意して設計されていると思う。
だが、外部キー制約は設定されていない。
働き始めた当初は外部キー制約を設定しないことに疑問を持っていて、外部キー制約を設定しない理由について考えたこともあったが、いつの間にか慣例として受け入れてしまっていた。
改めて、外部キー制約についての理解を深め、外部キー制約を設定しない理由について答えを出す。
参照整合性の担保
外部キー制約を貼る目的は、DBレベルで 参照整合性 を担保することである。
外部キーを設定する際に以下のオプションを設定できる。それぞれのオプション毎に挙動と問題点を整理する。
オプション
親テーブルのレコード（Parent）に対して、紐づく子テーブルのレコード（Child）とのリレーションに影響が出る UPDATE/DELETE の操作 をした場合の挙動 を下記の中から選択できる。
※ MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.17.2 外部キー制約の使用 を参考にした。
| オプション | 挙動 |
|---|---|
CASCADE |
Parent を UPDATE/DELETE すると、Child も自動で UPDATE/DELETE される。 |
SET NULL |
Parent を UPDATE/DELETE すると Child の外部キーを NULL にしてリレーションを破棄する。 |
RESTRICT |
Parent を UPDATE/DELETE しようとした時、Child が存在する場合エラーとなる（デフォルトの挙動）。 |
NO ACTION |
RESTRICT と同様。 |
自動で Child のレコードを削除してくれたり、NULL をセットしてくれたりするのは一見便利そうだが、下記の問題があると思う。
| オプション | 問題点 |
|---|---|
CASCADE |
仕様を把握していない人が操作をしたときに意図しない UPDATE/DELETE が発生する可能性がある。 |
| データの整合性を担保する方法が DB（自動で Child を UPDATE/DELETE してくれる）と アプリケーション側（その他の複雑な整合性）に分散するのは、バグのもとでは？ アプリケーション側にまとまっていたほうがよいのでは？ | |
| Child が大量にあった場合、大量の UPDATE/DELETE が自動で実行される。親テーブルのロックが長時間発生し、パフォーマンスの悪化やサービス障害につながると思う。 | |
SET NULL |
CASCADE と同様の問題。DELETE は走らないので、CASCADE よりはましかな。 |
SET NULL 以外の理由で外部キーに NULL が設定されないようアプリケーション側で担保しないといけないのでは。※ SET NULL 以外の理由で外部キーが NULL のレコードが存在しちゃったら、参照整合性がDBレベルで担保されているとはいえないような🤔 |
|
RESTRICT |
結局アプリケーション側に整合性を担保するためのロジック（Child → Parent の順で削除する 的な）を実装することになるから、外部キー制約が設定されていようがいまいがアプリケーション側に実装されるロジックは変わらないのでは。 ※ ただ、最後の砦として DB レベルで整合性を担保してくれるのは安心感がある。 |
| 参照整合性に限らず、データの不整合を防ぐためのトランザクション制御はアプリケーション側で実装することになる。特にマイクロサービスアーキテクチャにおいては複数のサービス、複数のDBにおける複雑な整合性（分散トランザクション）を実現しないといけない。参照整合性だけ外部キーで担保する必要があるのか（アプリケーション側で担保すればよいのではないか）。 | |
NO ACTION |
RESTRICT と同様。 |
ここまでまとめて思ったこと
CASCADEを利用するのは厳しいのでは？（SET NULLも微妙と思う）。- システムの性質として信頼性の高い手段で参照整合性を担保したい場合は、
CASCADEで外部キーを設定するのだろうか。金融系のシステムとかどうなっているのか気になる。 - システムの性質として外部キー制作を設定することが MUST ではない場合に、最後の砦として DB レベルで整合性を担保してくれる安心感が欲しい という理由で外部キー制約を設定するのは納得がいかない。が、多くの人はこの理由で外部キーを設定してたりして🤔
パーティショニング
外部キー制約が設定されているテーブルはパーティションを切れない。
これは外部キー制約を設定しない明確な理由となる。
InnoDB ストレージエンジン
InnoDB 外部キーと MySQL パーティショニングは互換性を持ちません。パーティション化された InnoDB テーブルは、外部キー参照を持つことができず、外部キーによって参照されるカラムを持つこともできません。外部キーを持っていたり、外部キーによって参照されたりする InnoDB テーブルはパーティション化できません。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 19.6.2 ストレージエンジンに関連するパーティショニング制限
ただ、パーティションを設定するかどうかに関係なく外部キー制約を設定していないのが現状なので、パーティショニング以外の理由を探る必要がある。
ロックについて
外部キー制約を設定している場合、 子テーブルに対して INSERT/UPDATE/DELETE が行われると親テーブルに共有ロックがかかるようだ。
ロックの順序に気をつけないと、デッドロックの原因となる。これは外部キー制約を設定する上で大きいデメリットと思う。
FOREIGN KEY 制約がテーブル上で定義されている場合は、制約条件をチェックする必要がある挿入、更新、または削除が行われると、制約をチェックするために、参照されるレコード上に共有レコードレベルロックが設定されます。InnoDB は、制約が失敗する> 場合に備えて、これらのロックの設定も行います。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.8 InnoDB のさまざまな SQL ステートメントで設定されたロック
一般的な MySQL と同様に、多数の行を挿入、削除、または更新する SQL ステートメントでは、InnoDB によって UNIQUE および FOREIGN KEY 制約が 1 行ずつチェックされます。外部キーチェックの実行時に、InnoDB は、調査対象の子または親のレコード上に共有の行レベルロックを設定します。InnoDB では、即座に外部キー制約がチェックされ、そのチェックはトランザクションのコミットまで遅延されません。SQL 標準によると、デフォルトの動作は遅延チェックにするべきです。つまり、SQL ステートメン> ト全体が処理されたあとにはじめて、制約がチェックされます。InnoDB で制約の遅延チェックが実装されるまで、外部キーを使用してそれ自体を参照するレコードを削除するなどの一部の操作が実行できません。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.6.6 InnoDB と FOREIGN KEY 制約
おわりに
整理してみて、僕が業務で開発している Web アプリケーションでは下記の理由で外部キー制約を設定していないのだと考えた。
- 共有ロックの問題があるため、規模の大きいサービスでは外部キー制約を設定するのはリスクが高い。
- アプリケーション側で参照整合性を担保することになるため、DB レベルで担保するよりも信頼性が落ちるが、そもそも外部キーでは実現できない複雑な整合性はアプリケーション側で担保する必要があるので、参照整合性をアプリケーション側で担保することは大きな問題にはならない。
こちらの考えを社内のエンジニアに共有して、答え合わせをしようと思う。
余談
調べている中で、ドメイン駆動設計の考え方をもとに外部キーを設定するべきかどうかの判断基準を示しているブログ記事（外部キー制約は何も考えずに適用するとよくない - かとじゅんの技術日誌 ）を見つけた。
自分はドメイン駆動設計について知識がゼロなので理解することは出来なかったが、ドメイン駆動設計という独自の観点（このブログ記事以外でドメイン駆動設計の観点で外部キー制約について説明しているものはなかったと思う）で自分なりの回答を示しているところが凄いなと思った。
自分もこのブログへのアウトプットをベースとした学習サイクルを繰り返し、自分なりの答えを示せるようになりたい。