using deferred on PK/FK relationships

From: "Dan Langille" <dan(at)langille(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: using deferred on PK/FK relationships
Date: 2002-10-22 15:58:55
Message-ID: 3DB53D7F.6311.23BC6765@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Can deferrable etc be used when deleting primary key records (master
table), then reinserting them without losing foreign key records
(slave table)? I ask because in our testing we can't; we lose the
foreign key records in the slave table. I'm guessing we are trying to
abuse the feature.

here's a test script we tried:

drop table master;
CREATE TABLE master (
id integer NOT NULL,
Primary Key (id)
);
insert into master values (1);
insert into master values (2);

drop table slave;
create table slave (
id int,
foreign key (id)references master (id) on update restrict on
delete cascade INITIALLY DEFERRED)
;

insert into slave values (1);
insert into slave values (1);

Then:

test=# BEGIN;
BEGIN
test=# SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
test=# delete from master;
DELETE 2
test=# insert into master values (1);
INSERT 20959595 1
test=# insert into master values (2);
INSERT 20959596 1
test=# select * from slave;
id
----
1
1
(2 rows)

test=# commit;
COMMIT
test=# select * from slave;
id
----
(0 rows)

test=#

Our hope was that after the commit, slave would retain the original
rows.

cheers
--
Dan Langille

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2002-10-22 16:03:13 Re: 7.2 date/time format function problems
Previous Message Ian Harding 2002-10-22 15:47:26 Re: 7.2 date/time format function problems