Re: foreign keys to foreign tables

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(at)gmail(dot)com> 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 <http://willjdunn.com>*
>
> On Mon, Jun 22, 2015 at 12:21 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Rick Otten <rottenwindfish(at)gmail(dot)com> 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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>