Postgres: foreign key to foreign table

Question

I have a foreign table, for example:

CREATE FOREIGN TABLE film (
    id          varchar(40) NOT NULL,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
)
SERVER film_server;

with id as the primary key for that table (set in the remote database). I would like to have a local table reference the foreign table, and set a foreign key constraint on the local table -- for example:

CREATE TABLE actor (
    id          varchar(40) NOT NULL,
    name       varchar(40) NOT NULL,
    film_id       varchar(40) NOT NULL,
)

ALTER TABLE actor ADD CONSTRAINT actor_film_fkey FOREIGN KEY (film_id) 
    REFERENCES film(id);

However, when I try to add the foreign key constraint, I get the error:

ERROR:  referenced relation "film" is not a table

Is it possible to add a foreign key constraint to a foreign table?

Pantheism

if i understand correctly, table actor and table file are on a different database? – Menology

Yes, the table actor and the table file are on different databases – Pantheism

Possible duplicate of PostgreSQL FOREIGN KEY with second database – Menology

you need to link the two databases first. before you can setup the table relationship. – Menology

I didn't include: CREATE SERVER film_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'remote_db', host 'example.com'); which links the local db to the remote db -- However, I'm not sure if this is sufficient for what I'm asking – Pantheism

sqlteam.com/forums/topic.asp?TOPIC_ID=101322, it seems what you're doing is not recommended at all. – Menology

Thanks for the link -- I definitely don't want to do it with triggers, and I wanted to see if things had changed since 2008 -- I found something a little more recent dbforums.com/…, but no definitive answer – Pantheism

I see we're lacking of clear defined proof that even in 9.6 foreign key on foreign tables are not supported. But in practice it's not possible. If you have any new updates on this issue, please, share it. I'm stack on the same problem as you. Thanks. – Acquainted

Answer 1

It's no possible create index on foreign tables.

CREATE INDEX idx_film ON film (id);

This is the error:

ERROR: cannot create index on foreign table

Jimmy