Обсуждение: 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