注目を集めつつあるMySQLプラットフォームのPlanetScaleですが、外部キー制約が効かないという一見致命的に見える仕様について調べていたところ、こちらのDiscussionで興味深い回答が開発者から寄せられていたので日本語でまとめ直してみようと思いました。
外部キー制約がなくてもそれほど困らない理由
今回の話はParentテーブル(id)とChildテーブル(id,parent_id)を前提に考えていきます
そもそも外部キー制約は何に役立つのか
今回のDiscussionでは質問者から「外部キー制約がないとこういう時に困るよ！」と質問が寄せられています：
- 外部キー制約がないと参照先のデータが存在していることを保証できない！
- 外部キー制約がないとデータの重複を回避できない！
それぞれの質問に対して回答者の回答は以下の通りです：
外部制約がなくても参照先のデータが存在することを保証したい
外部キー制約を作成するとき開発者は以下のオプションを選択できます：
-
on delete cascade-> Parentを削除すると紐づくChildも一緒に削除する -
on delete set null-> Parentを削除するとChildのparent_idをNULLにして関連を破棄する -
on delete no action-> Parentを削除しようとした時、そのParentを参照しているChildがあれば、削除に失敗する
それぞれの挙動が保証している事と、その価値について以下のように説明されています：
on delete cascade
- 「これは怖すぎる。採用するケースほぼないよね」
- 仮にChildが1000個紐づいていたらそれら全てが 自動的に 削除される恐怖、かつ広範囲にロックがかかる事によるパフォーマンス悪化が懸念事項。滅多に採用することはない
- 訳者メモ：（自動削除について）自分も同じ理由からcascadeを選ぶことはあまりない。Parentが消えたら1ピコ秒たりともChildが存在してはいけない！存在するなよ、絶対だ！絶対だぞ！！ とダチョウ倶楽部的な強い制約がDBレベルで課されている時に致し方なく採用するぐらいだけど、大体の制約は紐解いていくとそこまで強くない事が多い。あとcascadeは物理削除が前提になっているけど、「物理削除」という技術的な概念にドメイン上の概念が隠蔽されてしまうので物理削除しないケースも意外と多い
- 訳者メモ：（パフォーマンスについて）仮にParentを削除するイベントがユーザーからのアクション（ボタンクリックとか）で発生すると考えると、一刻も早くユーザーにアクションの結果を伝えたい時に大量のchildデータ削除を同期的に行うのが果たして妥当なのか？それならset nullにしておいて、サーバ負荷が低い時にparent_idがNULLなChildをバッチ処理で非同期的に削除しても良いのでは？という問いかけだと理解した
on delete set null
- 「外部キーが設定されていないのとほぼ同じ状況じゃね？」
- 1番の違いは、外部制約が存在しない野良データは明示的にNULLが設定されているためバッチが扱いやすいことだけど、仮にNULLが設定されていなくてもバッチでJOINすれば野良データは特定できる
- 訳者メモ：SET NULLしている間もユーザーは待たされるので（他のトランザクションが当該レコードを更新していたらロック待ちなど）、この辺もユーザーに対するレスポンスがどの程度求められるのか、バッチがどれだけ複雑なことをやっているのか、あたりのトレードオフになりそう
on delete no action
- 「どのみちDBの削除に関する知見はアプリケーションが知っておく必要が生じるよね」
- この設定だとParentを削除しようとした時にエラーが生じるため、Parentを参照している全てのChildを先に削除する必要がある。もしChildにさらにGranChildが存在すれば、GrandChildから先に削除するようアプリケーションが削除ロジックを制御する必要がある。となると結局DBの参照関係をアプリケーションが把握してParentを参照しているデータを事前に削除する必要があるため、外部制約があってもなくてもほぼ同じロジックがアプリケーションに実装される
- DBの外部制約だけでは表現しきれない複雑な整合性はどのみちアプリケーションで担保する必要があるから、外部制約に関する整合性もアプリケーション側で担保して良いんじゃない？
データの一意性はUnique制約で担保できる
「外部キー制約がなかったらデータの重複が防げない」→それはUnique制約の仕事
余談
外部キー制約が存在すると（cascadeやset nullだと）一見些細に見えた処理が非常に大きなトランザクションになる事があるが、外部キー制約が存在しないと2つのアプローチが選択できる：
- ボトムアップ
- 非同期トップダウン
ボトムアップな削除
今回の例だと、まずChildを削除した後にParentを削除する。こうすれば参照先のないChildが存在することはない。シンプル
非同期トップダウンな削除
Parentを削除して、参照先のない野良Childが存在することを許容する。野良Childは参照先がないのでJOINした時に取得されず、存在しないデータかのように扱われるため問題は生じない。あとはバッチで処理で負荷の低い時間帯に適宜野良データを削除すれば良い
思ったこと
ここまでがDiscussionで述べられていたことで、ここから先は個人的に思ったことなので読み飛ばしてもらって大丈夫です。
（感想）
on delete cascadeが不用意に定義されているDBを見かける事が意外と多いなぁと思っていたので非常にタイムリーな内容だった。また外部キー制約も「不整合データできたら困るしな」と脳死で使う事が多かったものの、削除時に非同期的に野良レコードを処理する方が適していることもあるので「果たしてこのデータは本当に外部キー制約が必要なのか」という観点も持ってみよう、と反省した。
ただ野良Childが存在する事が一切許容されないサービスであれば今回のDiscussionの項目は当てはまらないので、流石にその時はPlanetScaleを見送る必要がありそう。
DBではなくアプリケーション側でデータの整合性を担保することはもちろん可能だけど、それこそDELETEクエリを直接DBに対して実行したり、ユーザーが使うサービスとは異なる管理者用サービスから整合性を無視したDELETEが発行されてしまったり、アプリケーションに存在するルールはいつでも無視される危険性があるから、DBレベルで担保するよりは信頼度がだいぶ落ちるよね...
あと今回の話はDELETEに制限されているけど、UPDATEを考えると外部キー制約がないデメリットはより大きい気がする。アプリケーション側の実装ミスで存在しないparent_id=2にChildを紐づけてしまった時、外部制約がなければ都度チェックしない限り気付けない。「存在しないParentに紐づいたChildはどのみちParentとJOINしても取得できないから良いじゃん！」的なことが書いてあったけど、Childを単体で扱うロジックが生まれた時に事故りそう。Childを一覧表示して、クリックしたら参照先のparentに飛ぶとか
今回の説明には「DBレベルで絶対に担保しなければいけない強い整合性がない」という大事な前提があるので、この前提がサービスの性質に合致している場合にはPlanetScaleの採用もありなのかもしれない。ただ、いざ整合性が求められたときにRDBMSの恩恵を受けられないインフラにロックされるのはちょっと怖いなぁと思った
「いやいや、それは理解が違うぜ」など感じた点があればコメントいただけると大変勉強になり、嬉しいです・・・！
Discussion
初回設計開発時は外部キーついてても良いのですが、追加開発時の外部キー制約忘れで結局意味なくなったり、その後のリファクタやサービス分割において外部キー制約があることにより開発難度が上がってしまう場合が多く、個人的には基本は外部キーつけずにアプリケーション側で担保していくのが良いと思っています。
もちろんアプリケーション側のロジックで整合性を担保しようとしてもどこかで・いつかは抜けが発生するので、それが許されないようなプロジェクトでは外部キー制約を使った方がより安全になるでしょう。多分この許される・許されないの温度感が人それぞれ、時代にもより変わり外部キー制約論争になるのかなあと思いました。
あと最近ではRDBMS以外の永続化を組み合わせることも多く、NoSQLだったりS3のようなストレージだったりまたはDBを分割したりマイクロサービス化してたり多種多様で、外部キー制約だけで整合性を担保できるシチュエーションは少なくなっているのではないかなとも思います。