Re: referential integrity

Ian Turner wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> It seems that cascading across multiple tables does not work
> correctly, when combining different action types. For example, given:
>
> CREATE TABLE a (anum Integer PRIMARY KEY);
> CREATE TABLE b (bnum Integer PRIMARY KEY,
> anum Integer REFERENCES a ON DELETE CASCADE);
> CREATE TABLE c (cnum Integer PRIMARY KEY,
> bnum Integer REFERENCES b ON DELETE CASCADE,
> anum Integer REFERENCES a ON DELETE SET NULL);
>
> INSERT INTO a (anum) VALUES (1);
> INSERT INTO b (bnum, anum) VALUES (1,1);
> INSERT INTO c (cnum, bnum, anum) VALUES (1,1,1);
>
> This passes without an error:
>
> delete from b where bnum = 1;
> delete from a where anum = 1;
>
> but this fails:
>
> delete from a where anum = 1;
>
> with this error:
>
> ERROR: <unnamed> referential integrity violation - key referenced from c
> not found in b
>
> Got any ideas? :o

From the ri_triggers.c sourcecode in the area that checks for
key existance on UPDATE:

/* ----------
* Note:
* We cannot avoid the check on UPDATE, even if old and new
* key are the same. Otherwise, someone could DELETE the PK
* that consists of the DEFAULT values, and if there are any
* references, a ON DELETE SET DEFAULT action would update
* the references to exactly these values but we wouldn't see
* that weired case (this is the only place to see it).
* ----------
*/

What happens in your case is that the DELETE FROM a fires two
actions:

- DELETE the referencing row from b
- UPDATE the referencing row in c to NULL.

Of course, the DELETE FROM b "queues" another DELETE FROM c
because of the cascaded dependancy there. But at the time of
the cascaded UPDATE that hasn't happened, while the DELETE
FROM b has.

So the constraint on c.anum does an UPDATE ... SET NULL,
which in turn causes a check that c.anum and c.bnum exist in
a and b (c.anum's constraint is satisfied because it's NULL
now, but c.bnum's is violated).

Even if we wouldn't check because c.bnum didn't change, it
shouldn't work. That's because if the constraint on c.anum
already did an UPDATE to the row, it is not allowed to DELETE
it or UPDATE again in the same transaction. This is defined
as "triggered data change violation" in the SQL99 specs.

The only way to make it work is to add "INITIALLY DEFERRED"
to the "anum" constraint of table "c". In that case, the SET
NULL operation is delayed until transaction commit, and by
then all the DELETES already happened. Therefore, the SET
NULL action doesn't find any references to update.

Jan

--

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