Thread: Table inheritance foreign key problem
Hi,
One of the caveats described in the documentation for table inheritance is
that foreign key constraints cannot cover the case where you want to check
that a value is found somewhere in a table or in that table's
descendants. It says there is no "good" workaround for this.
What about using check constraints?
So say you've got cities and capitals from the example and you had some
other table that wanted to put a foreign key on cities (plus capitals).
For example, lets keep "guidebook" info for the cities. Some cities are
worthy of guidebooks even though they're not capitals. Rather than put a
foreign key constraint on "city", would the following work? What are the
drawbacks?
create table guidebooks (
city check (city in (select name
from cities)),
isbn text,
author text,
publisher text);
insert into guidebooks ('Barcelona', ....) -- not a capital
insert into guidebooks ('Edinburgh', ....) -- a capital
insert into guidebooks ('France', ....) -- fail
--
Andy Chambers
On Tue, Dec 21, 2010 at 9:32 PM, Andy Chambers <achambers@mcna.net> wrote: \ > create table guidebooks ( > city check (city in (select name > from cities)), > isbn text, > author text, > publisher text); This is a nice idea. They only problem is that PostggreSQL doesn't support sub-selects in a tables check constraints: http://www.postgresql.org/docs/9.0/interactive/sql-createtable.html -- Regards, Richard Broersma Jr.
Richard Broersma <richard.broersma@gmail.com> writes: > On Tue, Dec 21, 2010 at 9:32 PM, Andy Chambers <achambers@mcna.net> wrote: >> create table guidebooks ( >> city check (city in (select name >> from cities)), > This is a nice idea. They only problem is that PostggreSQL doesn't > support sub-selects in a tables check constraints: > http://www.postgresql.org/docs/9.0/interactive/sql-createtable.html And, before anybody says "what if I hide the sub-select in a function", here's the *real* problem with trying to use a CHECK constraint as a substitute for a foreign key: it's not checked at the right times. CHECK is assumed to be a condition involving only the values of the row itself, so it's only checked during insert or update. There is nothing preventing a change in the other table from invalidating your FK reference. There are some subsidiary problems, like dump/reload not realizing that there's any ordering constraint on how it restores the two tables, but the lack of a defense against deletions in the PK table is the real killer for this idea. regards, tom lane
On Wed, Dec 22, 2010 at 12:32:44AM -0500, Andy Chambers wrote: > Hi, > > One of the caveats described in the documentation for table > inheritance is that foreign key constraints cannot cover the case > where you want to check that a value is found somewhere in a table > or in that table's descendants. It says there is no "good" > workaround for this. For some values of, "good," there actually is. http://people.planetpostgresql.org/dfetter/index.php?/archives/51-Partitioning-Is-Such-Sweet-Sorrow.html http://people.planetpostgresql.org/dfetter/index.php?/archives/59-Partitioning-Glances.html Cheers, David (hoping PostgreSQL will be able to infer how to automate this some day). -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate