PostgreSQL tables creation with wrong order

Question

I have an .sql file, that creates lots of tables, that are related to each other.

I made other file for testing, that holds only two statements:

CREATE TABLE "USER" (
  "id" bigint NOT NULL,
  "name" varchar(50),
  PRIMARY KEY ("id"));
CREATE TABLE "PERSON" (
  "id" bigint NOT NULL,
  "name" varchar(50),
  "user" bigint,
  PRIMARY KEY ("id"),
  CONSTRAINT "fk_user" FOREIGN KEY ("user") REFERENCES "USER" ("id"));

This works fine if i'm trying to execute such file, but if i have other order - where table "PERSON" is created first - i'm getting ERROR: relation "USER" does not exist.

Is it possible to make some changes (or use some additional options when running 'psql' command), leaving the order as it is, to make it work?

EDIT: I understand why this error happens in given case, but i was thinking about some solution, where i don't need to change the order of my CREATE statements (Imagine you have hundreds of tables)... In MySQL you can simply use SET FOREIGN_KEY_CHECKS=0; and this will work. Do i have similar possibilities in PostgreSQL?

Craig Ringer · Accepted Answer · 2014-06-03 12:52:09Z

2

If you want table a to reference table b, you must either create table b before table a, or add the foreign key reference after creation:

ALTER TABLE a ADD FOREIGN KEY (a_col) REFERENCES b(b_col);

That works to create two tables that reference each other, too, but you won't be able to create rows unless you make one of them DEFERRABLE INITIALLY DEFERRED.

answered Jun 3, 2014 at 12:52

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user1322772 · Accepted Answer · 2014-06-03 12:55:27Z

1

You are getting the error because at the point you are creating the foreign key on the the PERSON table it references the USER table which does not exist yet.

You can work round this issue by separating out FOREIGN KEY CONSTRAINT into it's own statement and applying this after you have created both tables:

CREATE TABLE "PERSON" (
  "id" bigint NOT NULL,
  "name" varchar(50),
  "user" bigint,
  PRIMARY KEY ("id"));

CREATE TABLE "USER" (
  "id" bigint NOT NULL,
  "name" varchar(50),
  PRIMARY KEY ("id"));

ALTER TABLE "PERSON"
  ADD CONSTRAINT fk_user
  FOREIGN KEY ("user")
  REFERENCES "USER" (id);

answered Jun 3, 2014 at 12:55

user1322772

Collectives™ on Stack Overflow

PostgreSQL tables creation with wrong order

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related