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;