Re: referential integrity

On Thu, 31 Aug 2000, Ian Turner wrote:

> 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

I think I see what's happening.
It's doing the following order:
 Delete from b
 Update to c (which checks the keys and fails).
 [It would then do the delete from c but its already dead]

That could actually be a triggered data change violation actually since
the statement causes a row in c to be modified twice.  In fact, probably
any situation that could cause this sort of arrangement would fall into
this bracket, but there could be valid ones too.

The explicit cause is that the update is causing a check even though
the value isn't actually changed which might be bug in itself.  However,
I'm not sure that it's safe to change that, due to cases where if you
say did a ON DELETE SET DEFAULT, it should fail if you've deleted the
one value in the main table that is associated with that default
value (even if our value was the default before).