how to create a record when reference is required

From: java4dev <java4dev(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: how to create a record when reference is required
Date: 2011-05-03 09:17:47
Message-ID: 4DBFC83B.9090708@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello I have the following problem.
I am trying to create to tables that both reference each other
first try

CREATE TABLE A (
pk_a_id INTEGER NOT NULL,
fk_main_b_id INTEGER NOT NULL,
CONSTRAINT IXP_A PRIMARY KEY (pk_a_id),
CONSTRAINT RFR_MAIN_B FOREIGN KEY (fk_main_b_id) REFERENCES b(pk_b_id)
);

CREATE TABLE B (
pk_b_id INTEGER NOT NULL,
fk_owner_a_id INTEGER NOT NULL,
CONSTRAINT IXP_B PRIMARY KEY (pk_b_id),
CONSTRAINT RFR_MAIN_A FOREIGN KEY (fk_owner_a_id) REFERENCES a(pk_a_id)
);

this does not work as while defining table A, table B is not get
defined. So I changed it to

CREATE TABLE A (
pk_a_id INTEGER NOT NULL,
fk_main_b_id INTEGER NOT NULL,
CONSTRAINT IXP_A PRIMARY KEY (pk_a_id)
);

CREATE TABLE B (
pk_b_id INTEGER NOT NULL,
fk_owner_a_id INTEGER NOT NULL,
CONSTRAINT IXP_B PRIMARY KEY (pk_b_id)
);

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Willy-Bas Loos 2011-05-03 10:20:47 Re: [SQL] PLpgSQL variables persistance between several functions calls
Previous Message Pavel Stehule 2011-05-03 02:10:10 Re: [SQL] PLpgSQL variables persistance between several functions calls