Create foreign key on foreign table in postgresql

Question

I need to link my table with another one which is in different database(say table logs in device db and table accounts in user db, both on same server). So using Foreign Data Wrapper I create a foreign table(I check it with running select * from accounts; in device db which works successfully).

But I could not create a foreign key to foreign table from device db still

CREATE TABLE public.logs
(
  id bigint NOT NULL DEFAULT nextval('logs'::regclass),
  ...
  account_id bigint,
  ...

  CONSTRAINT logs_account_id_fkey FOREIGN KEY (account_id)
      REFERENCES public.accounts (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION

)

The error message is

ERROR:  referenced relation "accounts" is not a table
********** Error **********

ERROR: referenced relation "accounts" is not a table
SQL state: 42809

ypercubeᵀᴹ · Accepted Answer · 2018-07-19T13:21:05.727

You can't create a FOREIGN KEY constraint that references a table in either a different database or through a foreign data wrapper. A foreign key must reference a (base) table^* in the same database (a base table: not a view, not a foreign data wrapper table).

If you can change your design to have these 2 databases as 2 schemas in the same database then you can create the foreign keys without any problem.

^{*: there's also another restriction: a permanent table cannot reference a temporary table.}

Create foreign key on foreign table in postgresql

1 Answers1