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
– Ihor Romanchenko, Commented Oct 13, 2014 at 18:17
If need to do something like this, you'll have to use different schemas. It's definitely not possible across databases. — user330315
– user330315, Commented Oct 13, 2014 at 18:20
You could use triggers to enforce FK constraints and in turn maintain referential integrity! — Drew
– Drew, Commented Oct 13, 2014 at 18:42

Drew · Accepted Answer · 2014-10-13 18:41:56Z

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, 2014 at 18:41

answered Oct 13, 2014 at 18:19

Drew

2,7056 gold badges37 silver badges55 bronze badges

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

2 Comments

user330315 Over a year ago

Even with dblink I don't think you can create a FK to a different database.

Scott Stafford Over a year ago

> P.S. Would avoid this at all costs. 😂 So true...

khampson · Accepted Answer · 2014-10-13 18:51:24Z

3

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.

answered Oct 13, 2014 at 18:51

khampson

15.5k4 gold badges45 silver badges44 bronze badges

1 Comment

Crag Over a year ago

Foreign keys are not supported for foreign tables: stackoverflow.com/q/37380738/2199492

Collectives™ on Stack Overflow

PostgreSQL FOREIGN KEY with second database

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related