Re: How to replace rows in table so that foreign key rows

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).

> 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.