6

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?

smang
  • 1,187
  • 10
  • 23
  • if i understand correctly, table actor and table file are on a different database? – Dr. Stitch May 22 '16 at 23:55
  • Yes, the table actor and the table file are on different databases – smang May 22 '16 at 23:56
  • Possible duplicate of [PostgreSQL FOREIGN KEY with second database](http://stackoverflow.com/questions/26346222/postgresql-foreign-key-with-second-database) – Dr. Stitch May 23 '16 at 00:13
  • you need to link the two databases first. before you can setup the table relationship. – Dr. Stitch May 23 '16 at 00:15
  • 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 – smang May 23 '16 at 00:22
  • http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101322, it seems what you're doing is not recommended at all. – Dr. Stitch May 23 '16 at 00:24
  • 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 http://www.dbforums.com/showthread.php?1658453-Foreign-key-accross-different-databases, but no definitive answer – smang May 23 '16 at 00:28
  • 1
    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. – Hett Dec 22 '16 at 11:54

1 Answers1

0

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

Francisco
  • 143
  • 1
  • 8