Unique and exclusion constraints can be marked as deferrable, meaning that the user can choose to postpone the check to the end of the transaction, a nice way to relax constraints without reducing data integrity. However, as of PostgreSQL 10, there are a few limitations that you should be aware of; the restrictions are as follows:
- You must define a constraint as DEFERRABLE when creating it, either in the CREATE TABLE statement or in the ALTER TABLE statement. You cannot change an existing constraint from NOT DEFERRABLE to DEFERRABLE, nor can you make it INITIALLY DEFERRED or INITIALLY IMMEDIATE. If you need to apply such a change on an existing constraint, you need to create a new constraint and then drop the old constraint. Optionally, you can rename the new constraint at the end.
- You cannot mix deferrable unique constraints with foreign keys. You will get an error message if you try to add a foreign key that refers to a unique constraint that is deferrable.
It's likely that these restrictions will be lifted in later releases.