Re: Check/unique constraint question

Scott Rohde <srohde(at)illinois(dot)edu> writes:
> Tom Lane-2 wrote
>> Indeed, this illustrates perfectly why subqueries in CHECK constraints
>> are generally a Bad Idea: the constraint is no longer just about the
>> contents of one row but about its relationship to other rows, and that
>> makes the timing of checks relevant. Hiding the subquery in a function
>> doesn't do anything to resolve that fundamental issue.

> I don't think subqueries in CHECK constraints are a bad idea /per se/--to my
> mind it would depend on how they actually work. I don't know enough about
> the SQL standard or about products that support them to know if they work
> the way I /think/ they should work, which is basically this: "Guarantee that
> condition X (written as a constraint on table Y) is satisfied by the
> database when (1) the constraint is first added, and (2) whenever a change
> is made to one or more rows of table Y."

They certainly don't work like that in Postgres, and I doubt in other
DBMSes either. A CHECK constraint is assumed to involve only the contents
of a single row, and it's checked for each row when (actually before) that
row is inserted or updated.

There is a thing in SQL called an "assertion" which has the sort of
unconstrained semantics you imagine. Postgres doesn't implement those,
and we're not alone. The cost of enforcing them is nigh prohibitive.

regards, tom lane