CODEWITHSUNDEEP
sqldatabasepostgresqlforeign-keys
Wayne Werner
Wayne Werner

Reputation: 51867

Why does Postgres tell me that cross database references are not available when both tables are on the same database?

Here's the SQL:

CREATE TABLESPACE fnord1 location '/tmp/fnord1';
CREATE TABLESPACE fnord2 location '/tmp/fnord2';

CREATE SCHEMA IF NOT EXISTS fnord;

DROP TABLE IF EXISTS fnord.shea;
DROP TABLE IF EXISTS fnord.wilson;

CREATE TABLE fnord.shea (
    id  text not null,

    CONSTRAINT fnord_shea_id PRIMARY KEY (id) USING INDEX TABLESPACE fnord2
) TABLESPACE fnord1;

CREATE TABLE fnord.wilson (
    thing       text not null,
    shea_id     bigserial not null,

    CONSTRAINT fnord_wilson_id PRIMARY KEY (thing, shea_id) USING INDEX TABLESPACE fnord2,
    CONSTRAINT fnord_wilson_shea_fkey FOREIGN KEY (shea_id) REFERENCES fnord.shea.id
) TABLESPACE fnord1;

This fails because of the foreign key statement, with

ERROR: cross-database references are not implemented: "fnord.shea.id"

But... these are in the same database, are they not? If not, what's the right way to get them in the same database?

Upvotes: 1

Views: 7625

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175964

Use schema.table_name(column_name): 

CONSTRAINT fnord_wilson_shea_fkey FOREIGN KEY (shea_id) REFERENCES fnord.shea.id

to

CONSTRAINT fnord_wilson_shea_fkey FOREIGN KEY (shea_id) REFERENCES fnord.shea(id)

Upvotes: 2

Related Questions