Fwd: how to create a record when reference is required

---------- Forwarded message ----------
From: Willy-Bas Loos <willybas(at)gmail(dot)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(at)gmail(dot)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(at)postgresql(dot)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