How Do Deferred and Immediate Constraints Affect Primary and Unique Key Enforcement in Database Transactions?

Constraint Defined DEFERRABLE INITIALLY IMMEDIATE Remains Deferred

Enforcement of Deferred Constraints

Primary and unique key constraints defined as DEFERRED / DEFERRABLE and INITIALLY IMMEDIATE enforce uniqueness at specific points during transaction execution.

Deferred State

1. Update Statements Modifying Multiple Rows:

UPDATE statements modifying multiple rows involving primary key swaps succeed even with INITIALLY IMMEDIATE constraints, as the constraint check is deferred until after the statement.

2. Data Modifying CTEs:

Data modifying CTEs behave similarly, except for conflicting updates, which fail due to a non-deferred primary key violation.

3. Multiple UPDATE Statements in a Transaction:

Without SET CONSTRAINTS, multiple UPDATE statements within a transaction that violate unique constraints will fail, as the check is performed after each statement.

Immediate State

• Constraints defined with INITIALLY IMMEDIATE are enforced only after each statement.
• INITALLY IMMEDIATE and SET CONSTRAINTS IMMEDIATE behave identically.

Deferral and the PostgreSQL Implementation

• PostgreSQL enforces non-deferred constraints after each row update, leading to potential conflicts.
• DEFERRABLE constraints provide a workaround for this flawed implementation.

Summary of Constraint Enforcement

• DEFERRABLE INITIALLY IMMEDIATE constraints are checked after statements.
• DEFERRABLE INITIALLY DEFERRED constraints are checked after transactions.
• Non-deferred constraints are checked after every command.

Note: UNIQUE and PRIMARY KEY constraints are treated specially, and non-deferred constraints are checked immediately after every row modification.

Compatibility with SQL Standard

To achieve standard-compliant behavior, DEFERRABLE constraints should be declared as INITIALLY IMMEDIATE, but not deferred. This may compromise performance compared to immediate uniqueness checking.

Foreign Key Constraints

DEFERRABLE constraints cannot be used for FOREIGN KEY references, as referenced columns must be non-deferrable unique or primary key constraints.

The above is the detailed content of How Do Deferred and Immediate Constraints Affect Primary and Unique Key Enforcement in Database Transactions?. For more information, please follow other related articles on the PHP Chinese website!