今回作成したテーブルでは使用していないが,参照整合性制約チェックの遅延について書いておく.参照整合性制約は,参照側と被参照側が循環している場合など,指定の仕方によってはINSERTやUPDATEの処理が繁雑になることがある.そこで,トランザクションの任意のタイミングでチェックするように指定することができる.以下はトランザクションのコミット時まで参照整合性制約のチェックを遅延するものである.

...
CONSTRAINT 制約名
FOREIGN KEY (外部キー名)
REFERENCES 参照先テーブル名(参照先列名)
ON ...
DEFERRABLE INITIALLY DEFERRED
...

DEFERRABLEを指定すると,参照整合性制約チェックの遅延が可能となる.INITIALLY DEFERREDを指定すると,コミット時まで遅延するようになる.この場合に,トランザクションの途中で参照整合性制約をチェックしたくなったら,以下を実行する.

SET CONSTRAINTS 制約名 IMMEDIATE;

チェックに失敗したらトランザクションはアボートする.DEFERRABLEを指定しつつ,INITTIALLY DEFFEREDを指定していない場合は,トランザクション内で以下を実行すると,参照整合性制約のチェックをコミット時まで遅延する(※).

SET CONSTRAINTS 制約名 DEFERRED;

SET CONSTRAINTS 制約名 IMMEDIATE;

今回作成したテーブルではなかったが,当然ながら外部キーを複数していすることができる.外部キーにはNULL値が許されるが,複数の列の組み合わせが外部キーとなる場合,問題が生じる場合がある.

firstdb=> CREATE TABLE hoge (
firstdb(> id INTEGER,
firstdb(> name text,
firstdb(> PRIMARY KEY(id, name));

firstdb=> CREATE TABLE fuga (
firstdb(> id INTEGER,
firstdb(> name text,
firstdb(> FOREIGN KEY(id, name)
firstdb(> REFERENCES hoge(id, name));
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE

fugaはhogeのPRIMARY KEYであるid,nameのペアを外部キーとしてもつ.今hogeにはレコードがないが,fugaには以下のINSERTが可能である.

firstdb=> SELECT * FROM hoge;
 id | name 
----+------
(0 rows)

firstdb=> INSERT INTO fuga VALUES (1, NULL);
INSERT 26128 1
firstdb=> INSERT INTO fuga VALUES (NULL, 'a');
INSERT 26129 1
firstdb=> INSERT INTO fuga VALUES (NULL, NULL);
INSERT 26130 1
firstdb=> SELECT * FROM fuga ;
 id | name 
----+------
  1 | 
    | a
    | 
(3 rows)

これはどういうことかというと,id,name少なくとも一方がNULLなら残りの一方が参照先になくてもINSERTが成功してしまう.一方がNULLである時点で,もう一方のチェックをしないようだ.例えば(1,NULL)という組み合わせはhogeにはないのにfugaで外部キーとして使用できてしまう.これを防ぐにはREFERENCESの後に以下を指定する.

MATCH FULL

これを使用するfuga2を作成した.

firstdb=> CREATE TABLE fuga2 (
firstdb(> id INTEGER,
firstdb(> name text,
firstdb(> FOREIGN KEY(id, name)
firstdb(> REFERENCES hoge(id, name)
firstdb(> MATCH FULL);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE

fuga2にはid,nameの両方がNULLあるいは,id,nameの組み合わせがhogeになければINSERTできない.

firstdb=> INSERT INTO fuga2 VALUES (1,NULL);
ERROR:  <unnamed> referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values
firstdb=> INSERT INTO fuga2 VALUES (NULL,'a');
ERROR:  <unnamed> referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values
firstdb=> INSERT INTO fuga2 VALUES (NULL,NULL);
INSERT 26145 1
firstdb=> INSERT INTO hoge (1, 'a');
ERROR:  parser: parse error at or near "1"
firstdb=> INSERT INTO hoge VALUES (1, 'a');
INSERT 26146 1
firstdb=> INSERT INTO fuga2 VALUES (1, 'a');
INSERT 26147 1