PostgreSQL FOREIGN KEY with second database

Question

I'm running the following queries on PostgreSQL 9.3:

CREATE TABLE "app_item" 
  ( 
     "id"          SERIAL NOT NULL PRIMARY KEY, 
     "location_id" UUID NOT NULL 
  ); 

CREATE INDEX app_item_e274a5da 
  ON "app_item" ("location_id"); 

ALTER TABLE "app_item" 
  ADD CONSTRAINT app_item_location_id_5cecc1c0b46e12e2_fk_fias_addrobj_aoguid 
  FOREIGN KEY ("location_id") REFERENCES "fias_addrobj" ("aoguid") deferrable 
  initially deferred;

Third query returns:

ERROR: relation "fias_addrobj" does not exist

app_item - table in first database
fias_addrobj - table in second database

How to do correct query with this databases?

There is no way to create a FK to a table in other database. — Ihor Romanchenko, Oct 13 '14 at 18:17
If need to do something like this, you'll have to use different *schemas*. It's definitely not possible across databases. — , Oct 13 '14 at 18:20
You could use triggers to enforce FK constraints and in turn maintain referential integrity! — Drew, Oct 13 '14 at 18:42

Drew · Answer 1 · 2014-10-13T18:41:56.853

5

A local table must be referenced

However, as stated within the below link, you could maybe use a trigger which uses a cross server join (facilitated by dblink) to simulate the built-in methods for constraining?

For instance, you could have a trigger set up that on INSERT, checks to see if a given FK exists to aid with enforcing referential integrity, or on DELETE to cascade

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101322

P.S. Would avoid this at all costs.

edited Oct 13 '14 at 18:41

answered Oct 13 '14 at 18:19

Drew

2,583
5
36
54

Even with dblink I don't think you can create a FK to a different database. – Oct 13 '14 at 18:20
> P.S. Would avoid this at all costs. So true... – Scott Stafford Aug 24 '20 at 17:11

score 2 · Accepted Answer · answered Oct 13 '14 at 18:51

I've not had occasion to use this myself, but you might want to look into Foreign Data Wrappers, which are essentially the successor to dblink. In particular, postgres-fdw.

Once the general setup of the fdw is in place (steps 1-3 in the link above), you could create a foreign table via CREATE FOREIGN TABLE, defined like the table in your remote DB, and then use that table as part of the foreign key CONSTRAINT, and see if it works.

If that doesn't work, another option would be to have a process which ETL's the data (say, via a Python script) from the remote server over to the local server (say, on an hourly or daily basis, depending on the size), and then you would have a true local table to use in the foreign key CONSTRAINT. It wouldn't be real-time, but depending on your needs, may suffice.

Foreign keys are not supported for foreign tables: https://stackoverflow.com/q/37380738/2199492 — Crag, Oct 27 '17 at 15:49

PostgreSQL FOREIGN KEY with second database

2 Answers2

Linked