Thread: Fixing deferred foreign key checks

Fixing deferred foreign key checks

From
Stephan Szabo
Date:
I've added checks in the triggers for checking to
make sure the row on insert/update to fk is still
actually there at check time and that on noaction 
update/delete to pk that there wasn't a row added 
with this value.

With commenting out the triggered data change check
in the trigger manager this makes sequences like:
begin;
delete from pktable where key=<blah>;
insert into pktable (key) values (<blah>);
end;
work correctly for deferred triggers.

This brings up the question however of what to do
about other referential actions.  In the case above,
should we delete an fk row that was referenced by <blah>?
Deleting it seems wierd (although seemed to be what
oracle did when I tried it), but not deleting it seems
dangerous if you're using them to link sub-items to
a record (you re-use the number for someone new in that
transaction, do they get all the sub-items?)  And I don't 
have an SQL99 spec to see how restrict is supposed to act
although by the comments in the code, it looks like the
above should fail with restrict.

Last time this was brought up, I don't remember there 
being a consensus on what should be done with these cases
so I thought I should bring it up before doing any coding.