Re: When does a check constraint execute?

<Edmund(dot)Bacon(at)elb_lx(dot)onesystem(dot)ca>, ebacon(at)onesystem(dot)com writes:
> what? The check constraint should have prevented that insert, shouldn't it?

No, because a check constraint is only designed to check values within a
row. It's applied to the proposed row before insertion.

> I do not know whether this is a bug or not? I suspect that what's
> really happening is that I'm abusing CHECK constraints,

Yup. The main problem with a CHECK constraint that does sub-selects is
that alterations to other rows could make it no-longer-true, but the
check won't be reapplied to discover that. It's only semantically
consistent if you restrict the CHECK to checking the values of the row
itself.

SQL99 considers this an optional feature:

1) Without Feature F671, "Subqueries in CHECK constraints", the
<search condition> contained in a <check constraint definition>
shall not contain a <subquery>.

We don't support this, and in fact disallow you from writing such a
thing. You can make an end run around the error check by hiding the
subquery in a function (as you did), but that isn't going to result
in correct behavior. At least not without a lot more logic than this
function has got.

regards, tom lane