"Richard Broersma" <richard.broersma@gmail.com> writes:
> I am curious if the motivation is still valid for intentionally
> omitting check sub-queries. (what was the motivation to begin with?)
> Since we can effectively work around this limitation by doing the same
> thing with a function in a CHECK constraint, why would we want to
> prevent anyone from using the standard syntax for achieving the same
> effect?
Because if we supported the standard syntax, we'd also have to support
the standard semantics; which a function-in-CHECK does *not* give you.
The standard says that the constraint is guaranteed not to be violated,
which in the worst case means that any time you update the table(s)
referenced in the subquery, you have to retest the CHECK expression
at every row of the table having the constraint. Consider for instance
CREATE TABLE t1 (x int CHECK (x < (SELECT sum(y) FROM t2)));
If we change some value of t2.y, do all values of t1.x still satisfy
their constraint?
In some cases, with enough intelligence you could optimize this into
something fast enough to be usable; but it's a research problem.
(The cases that I can see how to optimize are pretty much equivalent to
plain foreign key constraints, anyway.)
regards, tom lane