Обсуждение: how to create a record when reference is required

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

On May 3, 2011, at 2:47 PM, java4dev wrote:

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


Try something like:
BEGIN;
set constraints all deferred;
INSERT statement;
INSERT statement;
COMMIT;

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com