Re: foreign keys to foreign tables - Mailing list pgsql-general

From Rick Otten
Subject Re: foreign keys to foreign tables
Date
Msg-id CAMAYy4Kr2idEAYnCDfDPRPVnjNKkf5za_JBaKXFBHPHm=61=7w@mail.gmail.com
Whole thread Raw
In response to Re: foreign keys to foreign tables  (Tom Lane <tgl@sss.pgh.pa.us>)
List 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:
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

pgsql-general by date:

Previous
From: William Dunn
Date:
Subject: Re: foreign keys to foreign tables
Next
From: Rick Otten
Date:
Subject: Re: foreign keys to foreign tables