
Well, I'm not sure about the spec precisely, but I found
out how Oracle seems to handle the delete/insert, etc
cases for at least on delete cascade.
It seems that they do the action (other than no action
obviously) immediately upon the statement, but still
defer the check for validity until commit time.

So, given:
create table a (a int unique);
create table b (b int references a(a)
                 on delete cascade deferrable 
                 initially deferred);
insert into b values (3);
insert into a values (3);
select * from a;  -- gives 1 row with 3
select * from b;  -- gives 1 row with 3
delete from a;
select * from b;  -- gives no rows
insert into a values (3);
commit;
select * from b;  -- gives no rows
select * from a;  -- gives 1 row with 3


This is related part 2 of how Hiroshi broke up the
issues with the deferred FK.  If Oracle's right, then
we'd probably want to:
 Make all non-No Action triggers not deferrable
 Add a check to the no action triggers to basically
  do a select from pk table where <each key value
  is the same as the old values> and if we get a
  row we've inserted a row with this same value
  so the constraint will be satisfied.

The latter fails for MATCH PARTIAL, but that's a 
completely different animal entirely...



Mon, 5 Feb 2001 14:20:30 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Foreign keys and deferrable