High Performance PostgreSQL for Rails: `CHECK` constraint can't stand in for `UNIQUE` constraint (Pages 76–77)

@andatki

The book states:

How can we safely add UNIQUE and NOT NULL constraints to tables, though, and make sure that all rows are eventually checked for compatibility with the constraint?

To do that, we can again use check constraints in a supportive, temporary role for UNIQUE and NOT NULL.

Even when the intended final constraint type is UNIQUE or NOT NULL, check constraints can stand in temporarily, to validate changes against new rows in preparation for adding the other constraint types on a permanent basis.

I understand and agree with the statements with regard to NOT NULL constraints, but I don’t think it’s possible to enforce uniqueness with a CHECK constraint. The PostgreSQL Glossary states:

Check constraint

A type of constraint defined on a relation which restricts the values allowed in one or more attributes. The check constraint can make reference to any attribute of the same row in the relation, but cannot reference other rows of the same relation or other relations.

If a CHECK constraint can’t reference other rows, then it can’t ensure that the current row doesn’t contain a value or values that are in another row.