Thomas! FOREIGN KEY problem!

Damned,

while hacking down a little test suite for FOREIGN KEY
(just to have some script based checking while doing
the file buffering of the event queue), I discovered
something looking wrong.

Having the following table schema:

CREATE TABLE t1 (
a int4 PRIMARY KEY,
b int4
);

CREATE TABLE t2 (
c int4,
d int4,

CONSTRAINT t2_d_t1_a FOREIGN KEY (d)
REFERENCES t1 MATCH FULL
ON UPDATE CASCADE
DEFERRABLE INITIALLY IMMEDIATE
);

I can do the following:

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
UPDATE t1 SET a = 99 WHERE a = 1;
UPDATE t1 SET a = 1 WHERE a = 2;
UPDATE t1 SET a = 2 WHERE a = 99;
COMMIT;

to swap t1.a 1<->2.

The result (due to my internal condensing of trigger
events) is, that all references to the OLD.a=1 will end
up by referencing to NEW.a=1. In fact, they should
point to 2. What I'm unable to figure out from the SQL3
specs is, what is the correct behaviour in this case?

The simple solution would be, to bomb out at the third
UPDATE with a "triggered data change violation"
exception. Rows, resulting from the first UPDATE
(identified by XMIN) are subject to change again, and
there are outstanding trigger events. Or must the
references follow exactly the above swap? Would be more
tricky, but IMHO possible anyway.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck(at)debis(dot)com (Jan Wieck) #