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. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
