2

I want to create foreign key in the current database table by referring other database table.

So i have decided to go with FDW.

I have done the following steps:

Step 1: Created extension

CREATE EXTENSION postgres_fdw;

Step 2: Created foreign server

CREATE SERVER foreign_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '127.0.0.1', port '5432', dbname 'testing');

    CREATE USER MAPPING FOR postgres
    SERVER foreign_server
    OPTIONS (user 'postgres', password 'password');

Step 3: Created foreign table

CREATE FOREIGN TABLE fgn_tbl_calculator 
(
  value1 integer,
  value2 integer,
  sum integer,
  sub integer,
  div integer,
  mul integer
)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'calculator');

Step 4: Creating table with foreign key

create table calculator_copy
(
  value1 integer,
  value2 integer,
  sum integer,
  sub integer,
  div integer,
  mul integer,
  primary key (value1),
  foreign key (value1) references fgn_tbl_calculator(value1)
);

But stuck with an error:

 ERROR:  referenced relation "fgn_tbl_calculator" is not a table
MAK
  • 6,824
  • 25
  • 74
  • 131
  • 3
    That's not possible. –  Jan 10 '17 at 12:05
  • @a_horse_with_no_name, Do we have any possible solution for this problem in other way? – MAK Jan 10 '17 at 12:07
  • The *solution* is: don't rely on the stability of *remote* foreign keys. – wildplasser Jan 10 '17 at 12:17
  • If you really need to maintain integrity against remote database you can think about using logical replication to sync given remote foreign table to your local database and then use standard local constraint. But this is very maximalistic solution... – JosMac Jan 10 '17 at 13:49
  • @a_horse_with_no_name what about a trigger `before insert` that checks the validity of the wanna-be-foreign-key and aborts the insertion if there's no proper key on the remote table? I don't know how to implement the abortion part – FLC Jan 29 '20 at 18:31

0 Answers0