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