外部キー参照しあうテーブルを遅延制約で実現する
先に結論。DEFERRED, DEFERRABLEという単語を覚えてお帰りくださいませ。
そして先にごめんなさい。SQL ServerとMySQLはDEFERRED, DEFERRABLEを未サポートですのでこの記事の内容は使えません。
さて話のはじめから。
こんな条件のデータを格納したくなったとしてください。
顧客は担当者をもつ。 歴代の担当者が複数いるかもしれないが、現在の担当者が必ず一人いる。
素直にER図で書くとこんな感じ。
テーブル同士が外部キー参照し合っています。あれ、そんなことできるんだっけ？ というお話です。
お互いに外部キー参照する
実際書いてみましょう。
-- 顧客 create table 顧客 ( 顧客ID INT NOT NULL PRIMARY KEY , 法人名 VARCHAR(64) NOT NULL , 現担当者ID INT NOT NULL , CONSTRAINT fk1 FOREIGN KEY (顧客ID,現担当者ID) REFERENCES 担当者(顧客ID,担当者ID) ) ; -- 担当者 create table 担当者 ( 顧客ID INT NOT NULL FOREIGN KEY (顧客ID) REFERENCES 顧客(顧客ID) , 担当者ID INT NOT NULL , 担当者名 VARCHAR(64) NOT NULL , メールアドレス VARCHAR(64) , 電話番号 VARCHAR(64) , CONSTRAINT pk1 PRIMARY KEY (顧客ID,担当者ID) ) ;
テーブル同士、お互いに外部キー制約をかけ合ってみます。コマンドは通る。
制約が設定できたようなので、まず顧客IDにレコードを追加してみましょう。
INSERT INTO 顧客 (顧客ID, 法人名) VALUES (1, 'はてな');
ここでいきなりNOT NULLに引っかかってエラーです。挿入できません。だって現担当者は必ずいるんだからNOT NULLにしようって決めたんですものね。まだ存在しないので設定しようがありませんが。
もちろん先に担当者を挿入しようとしてもエラー*1。
INSERT INTO 担当者 (担当者ID, 担当者名) VALUES (1, 'しなもん');
だって対応する顧客がまだありません。
相互の外部キー制約は書けても実際にはレコードが挿入できない、つまり使えないのですかと。
そうだ、BEFORE INSERTかAFTER INSERTのトリガを書いてそこで相方を挿入すれば！
やっぱりだめです。
そうだ、挿入直前に制約をはずして挿入直後に制約をかけ直せば！
実稼働アプリに読み書き以外の権限を与えちゃうんですかお兄さん（それ以前に外部キー制約のかけ直しはコスト高そう）。
PostgreSQLとOracleには遅延制約がある
遅延制約とは、トランザクションが終わるまで検査されない制約のことです。
そう、相互の外部キー制約を片方でも遅延制約にしてあげれば、双方のテーブルへの行挿入をひとつのトランザクションで実行する限りは引っかかりません。
顧客テーブルの方の外部キー制約はこう書いてあげましょう。
CONSTRAINT fk1 FOREIGN KEY (顧客ID,現担当者ID) REFERENCES 担当者(顧客ID,担当者ID) DEFERRABLE INITIALLY DEFERRED
DEFERRABLEが「遅延制約にできる」、INITIALLY DEFERRERDが「最初から遅延制約にしておく」の意味です。
このオプションにより、相互外部キー制約のかかったテーブル組にも行が挿入できるのでした。
DEFERRABLE INITIALLY DEFERRED、ぜひここぞのところで使ってみてください。
ひとつ次の記事ではこの方法の応用を紹介しています。
