How to replace rows in table so that foreign key rows are not deleted

From: "Andrus" <eetasoft(at)online(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: How to replace rows in table so that foreign key rows are not deleted
Date: 2006-04-20 14:56:56
Message-ID: e287fo$1v2m$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Berend Tober 2006-04-20 15:22:54 Re: How to replace rows in table so that foreign key rows
Previous Message Mavinakuli, Prasanna (STSD) 2006-04-20 14:50:30 Re: HUGE Stack space is gettiing consumed