> > FOREIGN KEY INSERT & UPDATE/DELETE in transaction "change violation"
>
> A well known issue, and I've asked multiple times how exactly
> we want to define the behaviour for deferred constraints. Do
> foreign keys reference just to a key value and are happy with
> it's existance, or do they refer to a particular row?
I think first. The last is closer to OODBMS world, not to [O]RDBMS one.
> Consider you have a deferred "ON DELETE CASCADE" constraint
> and do a DELETE, INSERT of a PK. Do the FK rows need to be
> deleted or not?
Good example. I think FK should not be deleted. If someone really
want to delete "old" FK then he can do
DELETE PK;
SET CONSTRAINT ... IMMEDIATE; -- FK need to be deleted here
INSERT PK;
> Consider you have a deferred "ON DELETE RESTRICT" and "ON
> UPDATE CASCADE" constraint. If you DELETE PK1 and UPDATE PK2
> to PK1, the FK2 rows need to follow, but does PK2 inherit all
> FK1 rows now so it's the master of both groups?
Yes. Again one can use SET CONSTRAINT to achieve desirable results.
It seems that SET CONSTRAINT was designed for these purposes - ie
for better flexibility.
Though, it would be better to look how other DBes handle all these
cases -:)
Vadim