Fwd: how to create a record when reference is required
| От | Willy-Bas Loos |
|---|---|
| Тема | Fwd: how to create a record when reference is required |
| Дата | |
| Msg-id | BANLkTikH5cUw5PtN=DXnr8o2tQibH82i5A@mail.gmail.com обсуждение исходный текст |
| Ответ на | how to create a record when reference is required (java4dev <java4dev@gmail.com>) |
| Список | pgsql-novice |
---------- Forwarded message ----------
From: Willy-Bas Loos <willybas@gmail.com>
Date: Tue, May 3, 2011 at 12:12 PM
Subject: Re: [NOVICE] how to create a record when reference is required
To: java4dev <java4dev@gmail.com>
It doesn't seem to make sense to do that. What do you gain by it?
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
From: Willy-Bas Loos <willybas@gmail.com>
Date: Tue, May 3, 2011 at 12:12 PM
Subject: Re: [NOVICE] how to create a record when reference is required
To: java4dev <java4dev@gmail.com>
It doesn't seem to make sense to do that. What do you gain by it?
But anyway, you might want to try:
ALTER TABLE A ADD CONSTRAINT RFR_MAIN_B FOREIGN KEY (fk_main_b_id) REFERENCES b(pk_b_id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE B ADD CONSTRAINT RFR_MAIN_A FOREIGN KEY (fk_owner_a_id) REFERENCES a(pk_a_id) DEFERRABLE INITIALLY DEFERRED;
begin;
insert into a values (1,1);
insert into b values (1,1);
insert into b values (2,1);
commit;
DEFERRED means that the constraint are checked when the transaction ends
HTH,
WBL
ALTER TABLE A ADD CONSTRAINT RFR_MAIN_B FOREIGN KEY (fk_main_b_id) REFERENCES b(pk_b_id);
ALTER TABLE B ADD CONSTRAINT RFR_MAIN_A FOREIGN KEY (fk_owner_a_id) REFERENCES a(pk_a_id);
which works. but when trying to insert records
insert into a values (1,1);
insert into b values (1,1);
insert into b values (2,1);
I get
ERROR: insert or update on table "a" violates foreign key constraint "rfr_main_b"
DETAIL: Key (fk_main_b_id)=(1) is not present in table "b".
********** Error **********
ERROR: insert or update on table "a" violates foreign key constraint "rfr_main_b"
SQL state: 23503
Detail: Key (fk_main_b_id)=(1) is not present in table "b".
which sounds reasonable error but how do I overcome this problem, when in the same transaction want to insert records in both tables which will leave database consistent at the end of the transaction.
brg
Nikolas
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
В списке pgsql-novice по дате отправления: