using deferred on PK/FK relationships
От | Dan Langille |
---|---|
Тема | using deferred on PK/FK relationships |
Дата | |
Msg-id | 3DB53D7F.6311.23BC6765@localhost обсуждение исходный текст |
Ответы |
Re: using deferred on PK/FK relationships
|
Список | 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
В списке pgsql-sql по дате отправления: