Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group