Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id B8F2A475ADD
	for <pgsql-sql@postgresql.org>; Tue, 22 Oct 2002 11:58:36 -0400 (EDT)
Received: from bast.unixathome.org (bast.unixathome.org [216.187.105.150])
	by postgresql.org (Postfix) with ESMTP id BA9CA476488
	for <pgsql-sql@postgresql.org>; Tue, 22 Oct 2002 11:58:33 -0400 (EDT)
Received: from wocker (wocker.unixathome.org [192.168.0.99])
	by bast.unixathome.org (Postfix) with ESMTP id EE8383F4B
	for <pgsql-sql@postgresql.org>; Tue, 22 Oct 2002 11:58:37 -0400 (EDT)
From: "Dan Langille" <dan@langille.org>
To: pgsql-sql@postgresql.org
Date: Tue, 22 Oct 2002 11:58:55 -0400
MIME-Version: 1.0
Subject: using deferred on PK/FK relationships
Message-ID: <3DB53D7F.6311.23BC6765@localhost>
X-mailer: Pegasus Mail for Windows (v4.02a)
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200210/276
X-Sequence-Number: 9993

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