how to create a record when reference is required
От | java4dev |
---|---|
Тема | how to create a record when reference is required |
Дата | |
Msg-id | 4DBFC83B.9090708@gmail.com обсуждение исходный текст |
Ответы |
Re: how to create a record when reference is required
|
Список | 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
В списке pgsql-novice по дате отправления: