Re: foreign keys to foreign tables

[Rick Otten <rottenwindfish@xxxxxxxxx>]

Thanks Will!

I had been considering setting up replication (using SymmetricDS - which we already use between other databases in our environment), but decided for this one check it was too much trouble.  I may change my mind on that point again after all if I end up with a lot of dependencies like this or run into performance issues.

On Mon, Jun 22, 2015 at 1:06 PM, William Dunn <dunnwjr@xxxxxxxxx> wrote:

Hello Rick,

As I understand it you are correct. Oracle/DB2/Postgres and I think the SQL Standards to not implement constraints against tables on foreign servers. Although it would be possible to develop the DBMS to handle such constraints in a heterogeneous distributed environment it would be unwise because of the poor performance and reliability of data sent over networks so DBMSs do not implement it. You would, as you suspected, have to use stored procedures to emulate some of the functionality of a foreign key but definitely think twice about the performance bottlenecks you would introduce. A more clever thing to do is use Slony, BDR, or triggers to replicate the foreign table and create the constraint against the local copy. In some other DBMSs the clever thing to do is create a materialized view and constraints against the materialized view (which achieves the same) but Postgres does not yet support such constraints against materialized views.

Will J. Dunn

willjdunn.com

On Mon, Jun 22, 2015 at 12:21 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Rick Otten <rottenwindfish@xxxxxxxxx> writes:
> Hello pgsql-general,
> I'd like to set up a foreign key constraint to a foreign table from a local
> table.

> ie, I have a column in a local table that I'd like to ensure has a value in
> the foreign table.

> alter mytable
> add column  some_column_id   uuid references myforeigntable(some_column_id)
> ;

> Unfortunately I get a "not a table" error when I try this.

>         ERROR:  referenced relation "myforeigntable" is not a table

> I'm thinking I'll have to write a function that checks for existance of the
> ids in the foreign table, and then put a CHECK constraint on using that
> function, but I thought I'd as first if there was a better way.

What's going to happen when the foreign server decides to delete some rows
from its table?

                        regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general