Keyless Entry（外部キー嫌い）
外部キーを使用しないアンチパターン
そもそも外部キーって？
他のテーブルのデータにを参照（依存）するようにカラムにつける制約。
- 親テーブル ⇨ 子テーブルに参照されるテーブル
- 子テーブル ⇨ 親テーブルを参照するテーブル
具体的に設定するとどうなるのか以下の例を見て欲しい。
外部キーは、親テーブルに存在しない値の登録ができない
親テーブル
|book_id (PK)|title|
|:---|:---:|:---:|
|1|エンジニアの本１|
|2|エンジニアの本２|
|3|エンジニアの本３|
子テーブル
| comment_id (PK) | book_id (FK) | comment |
|---|---|---|
| 1 | 1 | とても良い |
| 2 | 2 | まあまあ良い |
| 3 | 3 | どちらかと言えば良い |
| 4 | 4 | どちらかと言えばよくない |
↑ 追加したい
この場合、子テーブルに４つ目のデータを登録しようとしましたが親テーブルに「book_id が 4」のデータがないためデータを登録できない。
外部キーは、子テーブルに存在する値の削除できない
親テーブル
|book_id (PK)|title|
|:---|:---:|:---:|
|1|エンジニアの本１|
|2|エンジニアの本２|
|3|エンジニアの本３|
↑ 削除したい
子テーブル
| comment_id (PK) | book_id (FK) | comment |
|---|---|---|
| 1 | 1 | とても良い |
| 2 | 2 | まあまあ良い |
| 3 | 3 | どちらかと言えば良い |
この場合、親テーブルから「book_id が 3」のデータを削除しようとしましたが子テーブルで「book_id が 3」を参照しているため削除できない。
削除する場合、子テーブルから「book_id が 3」を参照しているデータを削除してから親テーブルのデータを削除する必要がある。
外部キーを設定することで
上記の例のように親がいないデータの登録、子のいる親の削除などができないため依存関係のあるテーブル間のデータに対して整合性を担保できる。
このアンチパターンの問題点とは
そもそも外部キーって？の部分で解説をしたが、外部キーを設定することで「依存関係のあるテーブル間のデータに対して整合性を担保できる。」というメリットがある。
.....と、いうことは使用しないことで「依存関係のあるテーブル間のデータに対して整合性を保つことが難しい。」というのがこのアンチパターンの問題点となる。
外部キーを使用しない場合
そもそも外部キーって？の例を外部キーを使用しないで行なった場合を例として使用する。
子テーブルにデータを追加する場合
|book_id (PK)|title|
|:---|:---:|:---:|
|1|エンジニアの本１|
|2|エンジニアの本２|
|3|エンジニアの本３|
子テーブル
| comment_id (PK) | book_id | comment |
|---|---|---|
| 1 | 1 | とても良い |
| 2 | 2 | まあまあ良い |
| 3 | 3 | どちらかと言えば良い |
| 4 | XXX | どちらかと言えばよくない |
↑ 追加したい
外部キーを使用しないため子テーブルのFKはない。
子テーブルに新しいデータ（4つ目のデータ）追加する場合、参照する「book_id」が存在するか確認する必要がある。
-- 親テーブルに「XXX（親のbook_id）」が存在するかチェックする。
SELECT book_id FROM '親テーブル' WHERE book_id = XXX;
-- 存在した場合にINSERT
INSERT INTO '子テーブル' (book_id, comment) VAlUES (XXX, 'どちらかと言えばよくない');
「XXX（親のbook_id）」が存在しない場合は子テーブルにデータを登録しない。
親テーブルのデータを削除する場合
親テーブル
|book_id (PK)|title|
|:---|:---:|:---:|
|1|エンジニアの本１|
|2|エンジニアの本２|
|3|エンジニアの本３|
↑ 削除したい
子テーブル
| comment_id (PK) | book_id (FK) | comment |
|---|---|---|
| 1 | 1 | とても良い |
| 2 | 2 | まあまあ良い |
| 3 | 3 | どちらかと言えば良い |
親テーブルから「book_id が 3」のデータを削除する場合も同様に子テーブルに自身を参照するデータがないか確認が必要。
-- 子テーブルで自身の「book_id」が参照されていないかチェック
SELECT book_id FROM '子テーブル' WHERE book_id = 3;
-- 存在しなかった場合DELETE
DELETE FROM '親テーブル' WHERE book_id = 3;
子テーブルにて参照されていたらデータを削除しない。
具体的な問題点
上記の例のようにデータの追加や削除をする際にSELECTをかけて参照性が壊れないか確認をした上でデータの追加や削除をしないといけない。
また、SELECTをかけて存在チェックをしている間に削除や追加があった場合に親のいない子ができてしまう。
不正なデータができないようにするには？
テーブルをロックして操作中にデータの削除や追加ができないようにする。
まとめ
参照整合性を担保するために外部キーを設定する。
不要な確認用のコードを削減でき、参照整合性も担保できる。
次回
Entity Attribute Value
参考文献
