A table constraint is a guarantee that must be satisfied by all of 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 of 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, but it's 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:
- Enabling the constraint on newer rows of a large table that cannot remain unavailable for a long time
- Enforcing the constraint on newer rows, while keeping older rows that are known to violate the constraint
The constraint is marked as NOT VALID to make...