From: Dan Langille <dan@langille.org>
To: pgsql-sql@postgresql.org
Subject: using deferred on PK/FK relationships
Date: Tue, 22 Oct 2002 11:58:55 -0400
Message-ID: <3DB53D7F.6311.23BC6765@localhost> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dan@langille.org
Subject: Re: using deferred on PK/FK relationships
In-Reply-To: <3DB53D7F.6311.23BC6765@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox