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 <dunn...@gmail.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 <t...@sss.pgh.pa.us> wrote: > >> Rick Otten <rottenwindf...@gmail.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@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > >
