How Does PostgreSQL's DEFERRABLE Primary Key Check Enforcement Work?

DEFERRABLE Primary Keys Check Enforcement

The behavior of a DEFERRABLE primary or unique key constraint depends on its specification. PostgreSQL considers the following enforcement scenarios:

1. DEFERRABLE INITIALLY IMMEDIATE

Constraints with DEFERRABLE INITIALLY IMMEDIATE are enforced after each statement, not each row update, as you might expect. This means that:

• In your example UPDATE statement modifying multiple rows, the constraint will not be violated because all updates occur within the same statement.
• Similarly, data modifying CTEs will work as intended, unless there is a separate statement that would violate the constraint.

2. DEFERRABLE INITIALLY DEFERRED

Constraints with DEFERRABLE INITIALLY DEFERRED are enforced at the end of the transaction, allowing for multiple updates within the same transaction. This is useful for enforcing constraints across multiple statements.

3. Non-Deferrable Constraints

Constraints declared as NOT DEFERRABLE are checked after each row update, making them stricter. This can lead to constraint violations even when the constraint would be satisfied at the end of the command.

Historical Perspective

In earlier versions of PostgreSQL, non-deferrable constraints were also checked after each row update. This behavior was considered a bug and subsequently corrected in PostgreSQL 9.1.

Practical Implications

• For standard-compliant behavior, use DEFERRABLE INITIALLY IMMEDIATE constraints.
• If FOREIGN KEY constraints are required, the referenced columns must be part of a non-deferrable unique or primary key constraint.
• DEFERRABLE INITIALLY DEFERRED constraints can improve performance by allowing multiple updates in a single transaction.

The above is the detailed content of How Does PostgreSQL's DEFERRABLE Primary Key Check Enforcement Work?. For more information, please follow other related articles on the PHP Chinese website!