On Thu, 20 Apr 2006, Stephan Szabo wrote:
> On Thu, 20 Apr 2006, Andrus wrote:
>
> > I want to replace ( delete and insert) records in master table .
> > I delete and insert record with same primary key.
> > I want that foreign key records are not deleted.
> >
> > I tried
> >
> > begin;
> > create temp table t1 ( pk integer primary key );
> > insert into t1 values(1);
> > create temp table t2 (fk integer );
> > alter table t2 add foreign key (fk) references t1 on delete cascade
> > deferrable initially deferred;
> > insert into t2 values(1);
> > -- Howto: set delete_constraint deferred
> > delete from t1;
> > insert into t1 values(1);
> > commit;
> > select * from t2;
> >
> > Observed: no rows
> >
> > Expected: t2 must contain one row.
> >
> > foreign key check and deletion should occur only when transaction commits.
>
> Actually, this looks like a case where SQL99 strongly implies that the
> action happens even for non-immediate constraints as part of the delete
> but SQL2003 changed that and we didn't notice. This should probably be
> reasonably straightforward to change I think (hope).
Hmm, actually, it's a little less straightforward than I thought, mostly
because I haven't seen something that seems to explicitly say what to do
for non-immediate constraints that happened before the commit in the 2003
spec, I'd guess do the action at commit time as well, but the wording of
the general rules talk about rows marked for deletion, but by the time of
the commit, those rows are not marked for deletion any longer, but
actually deleted as far as I can see and there doesn't appear (for
non-match partial constraints) seem to be a special case for the
referenced row coming back into existance as far as I can tell either.
> > Any idea ?
> > Is there any generic way to turn off foreign key constraints before delete
> > command in transaction ?
>
> Right now, probably nothing short of dropping and readding the constraint.
Or, if you're willing to patch, I think a first order approximation of
what you want might be to remove the special cases in trigger.c
(afterTriggerSetState) and tablecmds.c (createForeignKeyTriggers), but I
haven't tested that.