DBの外部参照制約を設定しない場合のリスクについて

Question

外部参照制約の有無について質問させてください

以下のテーブルで

dept	user
dept_id	user_id
-	dept_id

親：dept、子:userの関係があるとき、
一般的にはuser.dept_idに外部参照制約を付けると思います。

例えばこのとき仕様や運用上の理由(userが先に登録されて、それに合わせてdeptを登録するパターンがある等)で
user.dept_idに外部参照制約を付けないというDB設計はしてよいのでしょうか。

また、親子関係のある場合に外部参照制約を付けないときの、リスク・デメリット等を教えて頂きたいです。

Answer 1

かつては外部参照を付けると遅くなるからと言うことで付けない（ロジックで整合性を担保する）ということもありましたが、付けておいた方が確実ですので、現在では付けるべきでしょう。

ただ外部参照は参照される側で参照される条件に沿ったインデックスを持っていないといけないといった制約があったりするので、それを満たせないなら作れないと言うことにはなります。

あと困るのは……

• create table や drop table の際の順序が制限される（外部参照する側は外部参照されるテーブルより後に作成し、前に削除しないといけない）。まあ外部参照自体を外しておいてあとで設定すれば関係ありませんが
• 同様に、データのバルクアウト・バルクインの際の順序も考慮する必要がある（これも、ツールの類いでは自動で一時外しなどをして処理されることがあります）

Comments

[taksi15]

付けるのが妥当ですよね。。
ご回答ありがとうございました

Answer 2

外部参照制約は、値の整合性を保つという事が目的です。
オプションにもよりますが、基本的にはON UPDATE CACADE ON DELETE CACADEを設定すると思います。
参照元からレコードが削除されたら、参照先のレコードも削除されるというやつですね。

親が消えたらそれを参照している子も消えなくてはおかしいです。
それをDB側でやってもらうか、クライアント側でやるかという違いはありますが、
どう考えてもDB側でやったほうがミスがないです。

メリットは当然、整合性を保てる事です。
デメリットは、自分としては無いと考えています。
まずは基本的に設定するべきです。
逆に参照する必要があるものについて、参照制約をつけないと必ず整合性が取れないミスが生まれます。
通常において整合性取れなくていい場合ってなんなの？って感じです。

例外としては、ログをDBで保存したいケースです。
ユーザの行動履歴などをDBに保存するようなケースでは、
ユーザが退会したときに、ほんとにユーザのレコードを物理削除してしまうような運用の場合、
参照制約により関連レコードが消えてしまいます。
この場合は、ログテーブルに関しては参照制約を設定しないという事になりますが、
これはログであり、運用データではないので整合性が崩れていても問題がありません。
ログはあとで分析用に使ったりするもので、公開画面などに表示したりしないので
すでに存在しないレコードの情報があってもサービス側に影響はありません。

結論ですが、自分なら基本的に参照制約を設定しますが、
ケースバイケースの事があるため、
運用的に妥当であると判断したなら設定しなくても良いと考えます。

しかしながら繰り返しになりますけども、
あとで制約を外す事もできますし、まずは設定する。
開発中あるいは運用中に問題が起きたら外す。
というのが現実的と思います。
問題が起きた＝運用上、参照制約を外す必要性が生まれたという事なので。

Comments

[m.ts10806]

細かくて申し訳ない
「CASCADE」ですね。

[taksi15]

経験上、外部参照を付けないことが無かったので、
個人的にもとりあえず付けた方が良いのではという気がしています,データの整合性は大事ですよね。
ログの件も参考になりました。ありがとうございました。