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