From: | Willy-Bas Loos <willybas(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Fwd: how to create a record when reference is required |
Date: | 2011-05-03 10:23:16 |
Message-ID: | BANLkTikH5cUw5PtN=DXnr8o2tQibH82i5A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
---------- 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
From | Date | Subject | |
---|---|---|---|
Next Message | Vibhor Kumar | 2011-05-03 10:23:36 | Re: how to create a record when reference is required |
Previous Message | Willy-Bas Loos | 2011-05-03 10:20:47 | Re: [SQL] PLpgSQL variables persistance between several functions calls |