Peter Geoghegan <p...@heroku.com> writes: > On Fri, Jul 18, 2014 at 7:31 PM, Tatsuo Ishii <is...@postgresql.org> wrote: >> Has anybody tried to implement subquery in CHECK constaint? If so, >> what are issues to implement it? Or the feature is not worth the >> effort? Comments and/or opinions are welcome.
> I think the basic problem would be what the check constraint subquery > meant to the user, and how useful that is expected to be in general. Yeah. Check constraints are only well-defined to the extent that they constrain the contents of the current row independent of anything else. It's hard to conceive of a use-case for a subquery that wouldn't violate that in some fashion. I can certainly conceive of cases in which you want to constrain the contents of one table in terms of another's contents, sort of like foreign keys, but let's suppose that the particular invariant you have in mind isn't expressible as a foreign key. But you can write a CHECK subquery that captures what you want. Now what? There's a *lot* of complicated infrastructure needed to implement foreign keys, because they constrain both tables not just one. How would you invert a CHECK subquery to figure out what changes are allowed in the referenced table? Maybe you're willing to accept the special case in which you don't intend ever to change the referenced table, or are willing to take responsibility for not changing it in a way that violates the CHECK constraint for any existing row in the referencing table. So fine; all the system is supposed to do is check the constraint on every insert/update in the referencing table. I think the implementation issues would be (1) there's no support for doing any planning of subqueries in standalone expressions. This is probably just a small matter of programming, but still a hurdle to be jumped. (2) how would pg_dump deal with check constraints like these? At minimum it'd have to understand, or guess at, the dump ordering restrictions needed to allow data to be reloaded with such a constraint. I'm not sure this is much easier to solve than the general case of SQL assertions (which we have not got either). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers