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