foreign keys to foreign tables
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.
Thanks.
--
Rick Otten
rottenwindfish@gmail.com
Rick Otten <rottenwindfish@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
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@sss.pgh.pa.us> wrote:
Show quoted text
Rick Otten <rottenwindfish@gmail.com> writes:
Hello pgsql-general,
I'd like to set up a foreign key constraint to a foreign table from alocal
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 referencesmyforeigntable(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
Obviously the server will be able to delete those rows because it will be
completely unaware of this dependency.
So it is the implied reverse constraint (of sorts) that can't be enforced
which makes an FK based definition impossible.
For my particular use case, this shouldn't be a problem. The foreign table
is a reference table which does not typically experience deletes. I'll go
with a function for now. Since this happens to be a PostgreSQL-PostgreSQL
mapping I'll also consider mapping my table back the other way and then
putting a delete trigger on the foreign reference table to either cascade
or stop the delete once I decide which I'd rather do.
Thanks for the help!
On Mon, Jun 22, 2015 at 12:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Rick Otten <rottenwindfish@gmail.com> writes:
Hello pgsql-general,
I'd like to set up a foreign key constraint to a foreign table from alocal
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 referencesmyforeigntable(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
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@gmail.com> wrote:
Show quoted text
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@sss.pgh.pa.us> wrote:
Rick Otten <rottenwindfish@gmail.com> writes:
Hello pgsql-general,
I'd like to set up a foreign key constraint to a foreign table from alocal
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 referencesmyforeigntable(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