Reference on Foreign Table Postgres 9.5

Question

I am using oracle_fdw to connect from a postgres 9.5.3 to an Oracle database.

The server and user mappings have been created on the postgres db:

   CREATE FOREIGN SERVER foo FOREIGN DATA WRAPPER oracle_fdw OPTIONS (connection info..);
   GRANT USAGE ON FOREIGN SERVER foo TO my_user;
   CREATE USER MAPPING FOR my_user SERVER foo OPTIONS (user, password..);

Then the Foreign Table was created:

   CREATE FOREIGN TABLE FT_ORACLE(
   my_id integer,
   description character varying(40)) SERVER foo OPTIONS (schema, table);

The table I am trying to reference from:

   CREATE TABLE T_LOCALE(
   id bigint NOT NULL,
   oracle_id integer,
   description character varying(40),
   CONSTRAINT t_locale_pkey PRIMARY KEY(id));

Trying to add the constraint:

  ALTER TABLE T_LOCALE
  ADD CONSTRAINT fk_t_locale_oracle_id FOREIGN KEY (oracle_id)
  REFERENCES ft_oracle (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;

Results in:

   ERROR:  referenced relation "ft_oracle" is not a table.

Question: You cannot reference on foreign tables, correct? Because Oracle wouldn´t care if some other db has referenced onto it´s table and just edit/delete its entries.

Answer 1

Correct, you cannot have foreign key constraints from or to a foreign table.

The reason is that there is no way for PostgreSQL to enforce the correctness of the constraint, so it would be pretty useless.