複数テーブルで ID の一意性を保つ

質問

Debian GNU/Linux 3.1 で psql 8.1.2 を使用しています。
下記の要領でテーブルを作成し、２つのテーブルでIDが重ならないようにしようと意図しました。

CREATE TABLE t1 (
t1_id int2 UNIQUE NOT NULL,
t1_name text NOT NULL
);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_t1_id_key" for table "t1"
CREATE TABLE
CREATE TABLE t2 (
t2_id int2 UNIQUE NOT NULL,
t2_name text NOT NULL
);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "t2_t2_id_key" for table "t2"
CREATE TABLE
CREATE FUNCTION unique_t12_id(int2) RETURNS BOOLEAN AS
'SELECT NOT (
EXISTS(SELECT * FROM t1 WHERE t1_id = $1) AND
EXISTS(SELECT * FROM t2 WHERE t2_id = $1)
);'
LANGUAGE SQL;
CREATE FUNCTION
ALTER TABLE t1 ADD CONSTRAINT con_unique_t12 CHECK (unique_t12_id(t1_id));
ALTER TABLE
ALTER TABLE t2 ADD CONSTRAINT con_unique_t12 CHECK (unique_t12_id(t2_id));
ALTER TABLE

ところが実際は上手くいきませんでした。
INSERT INTO t1 VALUES(1, 'test1');
INSERT 0 1
INSERT INTO t2 VALUES(2, 'test2');
INSERT 0 1
INSERT INTO t2 VALUES(2, 'test3');
ERROR: duplicate key violates unique constraint "t2_t2_id_key"
# ここまでは期待通りの挙動です

INSERT INTO t2 VALUES(1, 'test4');
INSERT 0 1
ここで制約が働いて欲しかったのですが、素通りでINSERTされています。
下記のように、操作後の関数の戻り値は「偽」なのでこの操作は制約に引っかかると思うのですが
何故意図したように動かないのでしょうか。

SELECT *,unique_t12_id(t2_id) FROM t2;
t2_id | t2_name | unique_t12_id
-------+---------+---------------
2 | test2 | t
1 | test4 | f
(2 rows)

スペースが詰まって読みづらい箇所もありますが、よろしくお願いします。

回答No.3

moritan2

直接の回答ではありませんが、２つのテーブルで重ならないIDが必要なら、ID発行用に一つの SEQUENCE を作っておいて、どちらのテーブルもIDの値はここから取得すればよいのでは？

お礼

回答ありがとうございます。

２つのテーブルの関係を説明しておりませんでした。
t2 は t1 から抜き取ったレコードから作成されるテーブルで、
t1 にレコードが残ったまま t2 に挿入される…といったことが起こらないようにするのが
制約を設ける意図でした。

従って t1 のIDをSEQUENCEから作成するのは良いのですが、
t2 のIDには t1 のIDで使用されていた値がそのまま入りますので
SEQUENCEを使用するわけには行きません。

…もっとも、SEQUENCEを共有してIDを生成するケースであっても
各テーブルにわたって一意性を保つ制約は、それとは別に設けておきたいような気もします。

回答No.2

nora1962

PL/PGSQLだとうまくいきますね
CREATE or replace FUNCTION UNIQUE_t12_id(int) RETURNS BOOLEAN AS'
DECLARE
I INT;
BEGIN
SELECT INTO I T1_ID FROM t1 WHERE t1_id = $1;
IF FOUND THEN
RETURN FALSE;
END IF;
SELECT INTO I T2_ID FROM t2 WHERE t2_id = $1;
IF FOUND THEN
RETURN FALSE;
END IF;
RETURN TRUE;
END;'
LANGUAGE PLPGSQL VOLATILE;

お礼

またまたありがとうございます。

ご回答の関数では、やはり $1 がどちらか片方のテーブルにあった場合でも「f」が返ります。
が、制約としてはそれでいいのですね。ドキュメントのCHECKに関する記事には納得いきませんが（苦笑）。

私がやりたかったこと自体は nora1962 様の先の回答でも
今回の回答でも実現できますので、
ありがたく採用させていただきます。

なお、SQL関数で
CREATE FUNCTION unique_t12_id(int2) RETURNS BOOLEAN AS
'SELECT NOT (EXISTS(SELECT * FROM t1 WHERE t1_id = $1) OR EXISTS(SELECT * FROM t2 WHERE t2_id = $1));'
LANGUAGE SQL;
と表現しても良いようです。

回答No.1

nora1962

FUNCTION で複数のテーブルを参照しているとダメみたいですね。
CREATE or replace FUNCTION CHK_t1_id(int) RETURNS BOOLEAN AS
'SELECT NOT EXISTS(SELECT * FROM t1 WHERE t1_id = $1);'
LANGUAGE SQL VOLATILE;

CREATE or replace FUNCTION CHK_t2_id(int) RETURNS BOOLEAN AS
'SELECT NOT EXISTS(SELECT * FROM t2 WHERE t2_id = $1);'
LANGUAGE SQL VOLATILE;

ALTER TABLE t1 ADD CONSTRAINT con_unique_t12 CHECK ( CHK_T2_ID(T1_ID) AND CHK_T1_ID(T1_ID) );
ALTER TABLE t2 ADD CONSTRAINT con_unique_t12 CHECK ( CHK_T1_ID(T2_ID) AND CHK_T2_ID(T2_ID) );

だとうまくいくみたいです。

お礼

回答ありがとうございます。

回答の制約ですと
「この数字はt1にもt2にも存在してはならない」
という条件になりますね。
私が意図していたのは
「この数字はt1かt2、片方の存在は許可するが両方に存在してはならない」
という条件なんです。

実際に回答の制約で試したところ、やはり
SELECT bool(chk_t2_id(t1_id) AND chk_t1_id(t1_id)) FROM t1;
の結果は挿入できたすべての行で「f」でした。

postgresql 8.1.2 文書の「CREATE TABLE」に関するページ
( http://www.postgresql.jp/document/pg812doc/html/sql-createtable.html )
の中ほどにCHECK句について書かれた部分があり、そこでは
-------------------------------------------
行の挿入、更新操作の結果、式がFALSEとなる場合は、エラー例外が生成され、
挿入や更新によるデータベースの変更は行われません。
-------------------------------------------
とあったので、操作後の状態で制約条件を審査し、結果「f」なら差し戻すのかと思っていましたが
実際はどうやら挿入や更新される行の値を用いて審査するが、
その際に関数を使ったりする場合は操作前のデータベースが参照されるようですね。
CHECK句でサブクエリが使用できないのもそのあたりに関連しているのでしょうか…。