外部キー制約は重荷になるか

SQLアンチパターン 4章 キーレスエントリ(外部キー嫌い)より

外部キー制約によって、多少のオーバーヘッドが生じるのは事実です。しかし、以下にあげるように、他の選択肢と比べると、外部キーの方がより効率的であることがわかります。

本書では、外部キー制約を使うことによってデータを更新する時の事前チェックを省略できるので効率が良いという主張。

とはいえ、データベースは「外部キー制約に違反した」というエラーは返してくれるが、複数の制約がある場合にそこからどの外部キー制約に違反したかを取得する一般的な方法は無いので、ユーザーに詳細なエラーの内容を提供する必要がある場合は、やはり事前チェックが必要になる。

あるいは筋が悪いけど、頑張ればどの制約に違反したかをエラーメッセージから拾えるかもしれない。いちいち自分で書く気にはならないが、そういうライブラリがあったら面白そうだ。

MySQLの例

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`sqlap`.`Bugs`, CONSTRAINT `Bugs_ibfk_1` FOREIGN KEY (`reported_by`) REFERENCES `Accounts` (`account_id`))

PostgreSQLの例

ERROR:  insert or update on table "bugs" violates foreign key constraint "bugs_reported_by_fkey"
DETAIL:  Key (reported_by)=(1) is not present in table "accounts".

ちなみにMySQLの"23000"というのが制約違反を表すSQLSTATEと呼ばれるエラーコード。例えばPHPだとPDOException#getCode()で取得できる。

PostgreSQLも同様にエラーコードを返却するが、psqlで表示するには以下のようにする。

=> \set VERBOSITY verbose
=> insert into bugs(bug_id, reported_by) values(1,1);
ERROR:  23503: insert or update on table "bugs" violates foreign key constraint
"bugs_reported_by_fkey"
...

と、PostgreSQLでは外部キー制約違反を表す"23503"を返却する。つまりMySQLではNOT NULL制約や一意性制約でも同じ"23000"を返すのに対して、PostgreSQLではそれぞれ違うコードを返す。

もう一つ。外部キー制約を使うと、実運用データの整合性が保たれる一方、テストでちょっとだけデータを入れることが困難だったり、テストデータのメンテナンスが重荷になったりする。

そうならないためにうまくテスト設計をすることも大切かもしれないが、一時的に外部キー制約を無効にする方法がフレームワークに準備されていると便利かもしれない。

外部キー制約を無効にするには、ALTER TABLEで外部キー制約をDROP/ADDする他、 MySQLであれば SET FOREIGN_KEY_CHECKS=0、PostgreSQLであれば、ALTER TABLE ... DISABLE TRIGGER ALL (データベースのスーパーユーザー権限が必要) で一時的に外部キー制約のチェックを無効にすることができる。

4/13追記 続きを書きました

SQLアンチパターンメモ - iakioの日記