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