Re: Bug in FOREIGN KEY

> >     Think  I misinterpreted the SQL3 specs WR to this detail. The
> >     checks must be made per statement,  not  at  the  transaction
> >     level.  I'll  try  to fix it, but we need to define what will
> >     happen with referential actions in the  case  of  conflicting
> >     actions on the same key - there are some possible conflicts:
> > 
> >     1.  DEFERRED ON DELETE NO ACTION or RESTRICT
> > 
> >         Do  the referencing rows reference to the new PK row with
> >         the  same  key  now,  or  is  this  still  a   constraint
> >         violation?  I  would say it's not, because the constraint
> >         condition is satisfied at the end of the transaction. How
> >         do other databases behave?
> > 
> >     2.  DEFERRED ON DELETE CASCADE, SET NULL or SET DEFAULT
> > 
> >         Again  I'd  say  that  the  action  should  be suppressed
> >         because a matching PK row is present at transaction end -
> >         it's  not  the same old row, but the constraint itself is
> >         still satisfied.

I'm not actually sure on the cascade, set null and set default.  The
way they are written seems to imply to me that it's based on the state
of the database before/after the command in question as opposed to the
deferred state of the database because of the stuff about updating the
state of partially matching rows immediately after the delete/update of
the row which wouldn't really make sense when deferred.  Does anyone know
what other systems do with a case something like this all in a
transaction:

create table a (a int primary key);
create table b (b int references a match full on update cascade     on delete cascade deferrable initially deferred);
insert into a values (1);
insert into a values (2);
insert into b values (1);
delete from a where a=1;
select * from b;
commit;