Zenn
レバテック開発部
🐍

尾を噛む蛇としての外部キー制約 - 『SQLアンチパターン』のキーレスエントリ（外部キー嫌い）を考察する

もりた
2024/02/19に公開
SQL
#
外部キー
#
データベース設計
tech

これはなに

ども、レバテック開発部のもりたです。

今回は『SQLアンチパターン』のキーレスエントリ（外部キー嫌い）を考察します。またそのトレードオフについて確認したのち、デメリットをなるべく減らすための方策について考えます。タネ本は『SQLアンチパターン』、『失敗から学ぶRDBの正しい歩き方』、そして様々なWeb上の情報になります。

外部キーについて

外部キーと外部キー制約

キーレスエントリについて説明する前に、まずその嫌われている外部キーとその制約がどんなものなのか確認します。

外部キーとは他のテーブルを参照するキーのことで、以下２点の制約を満たしていないといけません。

  1. 参照しているキーが存在する
  2. 参照をしない（外部キーのカラムがNULLである）

具体例を下記に示します。

■面談予定

面談予定ID 担当カウンセラーID 利用者ID 実施時間 相談種別ID 相談内容 支払い済み
1234 49 88 2024-06-23 1 職場の冷蔵庫に使いかけの牛乳が多く残っており、悩んでいます[…] 確認済み
1235 47 409 2024-06-23 NULL NULL 未確認

■カウンセラー

カウンセラーID 氏名
47 曽根綾亜子
49 滝口圭一郎

■相談内容マスタ

ID 相談内容
1 職場での人間関係
2 両親との関係性

この例では「担当カウンセラーID」「利用者ID」「相談種別ID」が外部キーになります。それらのカラムに入力できるのはそれぞれ参照先の親テーブルに存在する主キーのIDか、もしくはNULLです。

設定可能なreference_optionオプション

reference_optionとは、外部キーとして参照されている親テーブル側のレコードを削除・更新したときに、参照している子テーブルの外部キーをどうするか？　というオプションです。利用するRDBMSによりますが、だいたいのRDBMSで以下３種類の設定が可能になります。（MySQLだとこちらのページの参照アクションを参照。）

CASCADE

親テーブルの削除・更新を行ったのち、その更新内容を子テーブルにも反映させます。

SET NULL

親テーブルの削除・更新を行ったのち、子テーブルの外部キーをNULLで埋めます。

RESTRICT(NO ACTION)

親テーブルで参照のあるデータを削除・更新しようとした際にエラーを返します。

ただ、RESTRICTはトランザクションの時点でエラーを返し、NO ACTIONは設定次第で遅延チェックが可能です。この動作や設定の仕方は今回は割愛しますが、利用するRDBMSに依存し少なくともMySQLには存在しないようです。（NO ACTIONとRESTRICTが同じで遅延検査されない）

キーレスエントリ（外部キー嫌い）

さて、本題のキーレスエントリについてです。キーレスエントリとは、外部キーを設定することで生まれる制約を嫌って、適切な外部キーの設定をしないことです。そのメリットとデメリットは以下の通りです。

メリット

1. 開発容易性：制約を取り払うことで開発が容易になる

外部キーを設定すれば、外部キー制約を守ってデータの更新や削除をすることになります。それを意識しなくて良くなることで、開発が容易になります。

2. パフォーマンスの向上：デッドロックの回避

MySQLでは外部キーの設定された子テーブルでレコードの更新・削除すると、親テーブルを行レベルでロックします。それによって思わぬデッドロックが発生したり、そのデッドロック回避のためにパフォーマンスが犠牲になることがあります。

3. 外部キーを設定した際の思わぬ挙動によりデータ不整合が起きる

外部キーを設定した際のCASCADEなどの挙動を把握しきれておらず、データの不整合が起きることもあります。

デメリット

1. データの不整合

制約を取り払えば、データの不整合を防ぐことが出来ません。そのため、次のデメリットを引き起こします。

2. 全ての場面で完璧なSQL実行が求められる

データベースレベルで制約がかかっていない場合、全ての場面で完璧なSQL実行が求められます。実装だけであれば良いですが、データ保守業務でも常に外部キーの整合性を意識しなくてはなりません。もしユーザーに管理権限でデータの編集を許すとなった場合、ユーザーにまで高いリテラシーが求められることになります。一度不整合なデータができれば、それを修正するための不要な運用業務が発生することになるかもしれません。

『SQLアンチパターン』のまとめ

ここまでは主に『SQLアンチパターン』を元に外部キーレス設計にはどんなメリットがあり、デメリットがあるのか？　というのをみてきました。データベースの責務はデータを守ることにあるため、基本的には制約をなくすことによるデータ不整合は回避すべきです。

というわけで外部キー制約は設定していきましょう！　というのが『SQLアンチパターン』における解説と理解しているのですが、一点疑問が残ります。それは外部キーを設定することでデータ更新・削除時に思わぬ挙動が起きうる、という点です。これは開発者の認識不足が引き起こすものではありますが、そもそもその根底には外部キー制約のオプションが制約として「強過ぎ」、「失われた事実」を生み出すせいだとも思います。データを守るために設定した外部キーがデータを壊す、いうなれば外部キーは、自らの尾を噛む蛇のような状態になっているのではないでしょうか？

そこで以降、データ不整合を引き起こさないための外部キーの設定方法ということで方策を考えてみました。

提案：適切なオプション設定

「不整合からデータを守る」という点についてもう少し細かく考えてみたいのですが、これはふたつの要素に切り分けることが可能です。まずひとつ目が不整合データの生成を検知することで、ふたつ目はその不整合なデータを整合性のある状態にするということです。そしてその境界で、データベースとアプリケーションの責務は分離できるのではないでしょうか？

つまり、

  1. 不整合なデータを生み出す更新・削除を禁止する
    1. データベースの責務
  2. 整合性のあるデータ状態で更新・削除を実現する
    1. アプリケーションの責務

ということです。

これを実現可能にするのがNO ACTIONオプションです。NO ACTIONオプションは親テーブルでのデータの更新・削除があった際の子テーブルの動作を指定せず、ただ禁止するのみです。これがあれば不整合なデータを作ることはただ許されず、ユーザーは不正な操作を行った際にそれに気がつくことが出来ます。気がつくことができれば、トランザクションを貼るなりして正しい操作をすることが可能なはずです。

もちろん、CASCADEやSET NULLが必ず正しいとわかっているときはそちらを設定するのが良いと思いますが、責務としてはここで区切るのが適切ではないでしょうか。

またオプション周りに関しては「外部キーが一切ないと何が困るのか？ - Zenn」という記事が面白いです。内容としては、何も考えずに外部キーを設定するのはやめようね、というのと、各オプションの実際のうま味についての議論です。

おわりに - そもそも論だけどトレードオフの認識が大切

この記事では『SQLアンチパターン』のまとめと、そこで回収しきれていないデータ不整合についてもう少し細かく考えてみました。提案部分に関しては特に何かの書籍を参照しているわけではないため、なにか指摘事項があればいただけると幸いです。

また、この記事では意図的に深く触れていませんが、開発容易性やパフォーマンスなどの観点を考慮したとき、データ不整合のリスクとどちらを取るのかというのは開発するシステムで重視される非機能要件によります。いろんな資料を読みながら思いましたが、外部キーに関する議論は論点が比較的豊かなので、それぞれの観点から考えてみることも重要だろうなと感じています。

最後に参考にした資料や、そこで議論されている内容を記載しましたので、面白そうなのがあれば見てみてください。

参考資料（とその他の論点）

  • Webの資料
  • 書籍での言及
    • 『失敗から学ぶRDBの正しい歩き方』
      • 「強過ぎる制約」についての言及
        • 外部キー制約によるデッドロックの指摘
        • 外部キーオプションについてではないが、制約（RDB側）と規約（アプリケーション側）という記述もあり
        • 制約は適宜弱めてバランス感覚大切にしながら課すべき、との意見
      • 「失われた事実」についての言及
        • そもそも誤ってCASCADEするならそれは「誤った制約」であり、「失われた事実」でもある
    • 『SQL実践入門──高速でわかりやすいクエリの書き方』
      • 「ミドルウェアが自動的にぐるぐる系のSQLを実行する例としては、DBMSの外部キー制約においてCASCADE DELETEやCASCADE UPDATEを利用した場合などがあります。たとえばOracleでは、親テーブルが更新されたとき、CASCADEオプションによる子テーブルの更新は、１行を更新するSQL文が繰り返し発行されるという内部動作をするため、大量データの更新時に性能問題になることがあります。かつ、これはDBMS内部の動作であるためユーザが制御できず、チューニングが困難です」（p126）
      • Oracle使うなら割とキーレスでいいかも…？
    • 『SQLアンチパターン』

special thanks

この記事を書くにあたって、日本PostgreSQLユーザ会の方々に基本的なアイデアについてご意見いただきました。ありがとうございます。なおこの記事の文責は私にあり、日本PostgreSQLユーザ会さまの公式の意見を表明するものではありません。

レバテック開発部
レバテック開発部

レバテック開発部の公式テックブログです！

Discussion