Re: Check/unique constraint question

Scott Rohde <srohde(at)illinois(dot)edu> writes:
> There is something a bit odd about this solution: If you start with an empty
> table, the constraint will allow you to do

> INSERT INTO foo (active, id) VALUES ('t', 5);

> But if you insert this row into the table first and /then/ try to add the
> constraint, it will complain that an existing row violates the constraint.

> This begs the question of when constraints are checked.

> I had always thought of constraints as being static conditions that (unlike
> some trigger condition that masquerades as a constraint) apply equally to
> existing rows and to rows you are about to add. This seems to show that not
> all constraints work this way.

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.

The original example seemed to work for retail inserts because the check
gets applied before the row is physically inserted. It would fail on
updates though, or when trying to add the constraint after the fact.

regards, tom lane