Richard Broersma <richard.broersma@xxxxxxxxx> writes: > On Tue, Dec 21, 2010 at 9:32 PM, Andy Chambers <achambers@xxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
