Re: foreign keys to foreign tables

From: Rick Otten <rottenwindfish(at)gmail(dot)com>
To: William Dunn <dunnwjr(at)gmail(dot)com>
Cc: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: foreign keys to foreign tables
Date: 2015-06-22 17:16:30
Message-ID: CAMAYy4Ln=2JPABv7-aNwywU+RMYpjpAFy2ykRh-XEuU6OQDybA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
>>
>
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Christian Ramseyer 2015-06-22 17:39:52 Re: How to speed up pg_trgm / gin index scan
Previous Message Rick Otten 2015-06-22 17:12:21 Re: foreign keys to foreign tables