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@Yahoo.com #