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.
Any idea ?
Is there any generic way to turn off foreign key constraints before delete
command in transaction ?
Andrus.
