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  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список 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 по дате отправления:

Предыдущее
От: Nicholas Barthelemy
Дата:
Сообщение: 7.2 date/time format function problems
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: 7.2 date/time format function problems