CODEWITHSUNDEEP
postgresqlddl
drafterr
drafterr

Reputation: 623

How to add a foreign key for table B by referring of table A and foreign key for table A by referring table B in PostgreSQL?

I need to add a foreign keys for two tables each other. Can this be done ?

As an example: 

CREATE SCHEMA IF NOT EXISTS schema1;

CREATE TABLE schema1.tableA
    (
        id serial NOT NULL,
        tableB_id integer,
        PRIMARY KEY (id),
        FOREIGN KEY (tableB_id) REFERENCES schema1.tableB (id)
    );

    CREATE TABLE schema1.tableB
    (
        id serial NOT NULL,
        tableA_id integer,
        PRIMARY KEY (id),
        FOREIGN KEY (tableA_id) REFERENCES schema1.tableA(id)
    );

Above query causes an error !

ERROR: relation "schema1.tableb" does not exist SQL state: 42P01

Can this be done or is there any better solution? I'am using PostgreSQL version 10.5 and pgAdmin 3.6

Upvotes: 0

Views: 46

Answers (1)

user330315
user330315

Reputation:

Add the foreign keys after creating both tables:

CREATE SCHEMA IF NOT EXISTS schema1;
CREATE TABLE schema1.tableA
(
    id serial NOT NULL,
    tableB_id integer,
    PRIMARY KEY (id)
);

CREATE TABLE schema1.tableB
(
    id serial NOT NULL,
    tableA_id integer,
    PRIMARY KEY (id)
);

ALTER TABLE schema1.tablea
  add FOREIGN KEY (tableB_id) REFERENCES schema1.tableB (id);

ALTER TABLE schema1.tableb
  add foreign key (tableA_id) REFERENCES schema1.tableA(id);

Upvotes: 2

Related Questions