CODEWITHSUNDEEP
postgresql
Rápli András
Rápli András

Reputation: 3923

Postgres foreign keys / schema issue

If I create a new schema on the current database (management), why does it complain about cross-database references?

management=# create schema mgschema;
CREATE SCHEMA

management=# alter table clients add column task_id int null references mgschema.tasks.id;
ERROR:  cross-database references are not implemented: "mgschema.tasks.id"

Upvotes: 0

Views: 67

Answers (2)

user330315
user330315

Reputation:

A simple references only expects a table name. The foreign key will then automatically point to the primary key of that table, e.g. 

alter table clients add column task_id int null references mgschema.tasks;

Another alternative is to to specify the table and columns, but not with a single identifier:

alter table clients add column task_id int null references mgschema.tasks (id);

The second format is needed if the target table has multiple unique constraints.

Upvotes: 1

McNets
McNets

Reputation: 10807

alter table clients add column task_id int null references mgschema.tasks.id;

The REFERENCES syntax in not correct, you should use:

REFERENCES reftable [ ( refcolumn ) ]

Upvotes: 1

Related Questions