PostgreSQL 9 Administration Cookbook - Second Edition

Adding a constraint without checking existing rows

A table constraint is a guarantee that must be satisfied by all the rows in the table. Therefore, adding a constraint to a table is a two-phase procedure: first, the constraint is created, and then all the existing rows are checked. Both happen in the same transaction, and the table cannot be accessed in the meantime. The constraint becomes visible after the check, yielding perfect consistency—which is usually the desired behavior—at the expense of availability, which is not that great.

This recipe demonstrates another case—how to enforce a constraint on future transactions only, without checking existing rows. This may be desirable in some specific cases, such as the following two:

• Enabling the ...