If two tables A and B need to have mutual foreign key references, each foreign key constraint can be defined in the create table statements for A and B. I am trying to do this in pgAdmin, but its showing error. I want to just verify is this statement is true or false. I have a doubt that in order to add a foreign key reference in child table. It should have the parent table. Can some confirm this ?
CodePudding user response：
Yes you can have mutual foreign key references, but you can't define both of them using only create table. The reason is that when you define the first table, the second table doesn't exist yet, so you can't reference it in a foreign key.
create table A (id serial primary key, Bid serial references B);
create table B (id serial primary key, Aid serial references A);
Returns this error at the create table A:
Schema Error: error: relation "b" does not exist
And because A failed to be created, the create table B also returns an error:
Schema Error: error: relation "a" does not exist
Instead, you need another step. First create table A, then create table B with its foreign key reference to table A, then add the foreign key to table A. Now that table B exists, you can reference it.
create table A (id serial primary key);
create table B (id serial primary key, Aid serial references A);
alter table A add Bid serial references B;