Thread: using deferred on PK/FK relationships

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

On Tue, 22 Oct 2002, Dan Langille wrote:

> 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.

> 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.

As far as I can tell the above is close to right (I'd have said that
the select in the transaction should have given you 0 rows as well
but that's a matter of argument).  In case you're wondering, the
spec says for match full/unspecified something to the effect of:
when a row is marked for deletion that has not previously been marked
for deletion with on delete cascade all matching rows are marked for
deletion.  So, I don't think you can get the effect you're looking
for that way.  Someone else mentioned this recently and I was thinking
that it might be a useful extension to add another referential action
to handle it (and it wouldn't be particularly hard probably).