When Are DEFERRABLE PRIMARY/UNIQUE Key Constraints Actually Enforced in PostgreSQL?

DEFERRABLE INITIALLY IMMEDIATE Constraints: Execution Enforcement

Question:

What is the exact point at which DEFERRABLE / DEFERRABLE/ IMMEDIATE unique/primary key constraints are enforced?

Answer:

According to Tom Lane, a prominent PostgreSQL core developer, the current behavior is intended and will not be changed. The constraint is considered satisfied if it holds true at the end of the command, even if it is violated during intermediate steps.

Summary of Constraint Enforcement:

• NOT DEFERRABLE UNIQUE/PRIMARY KEY Constraints: Checked after each row.
• DEFERRABLE Constraints (IMMEDIATE): Checked after each statement.
• DEFERRABLE Constraints (DEFERRED): Checked after each transaction.

Exceptions:

• NOT DEFERRABLE Uniqueness Constraints: Despite the SQL standard dictating enforcement at the end of the statement, PostgreSQL checks for uniqueness immediately after every command for UNIQUE/PRIMARY KEY constraints.

Workaround:

To comply with the SQL standard, declare the constraint as DEFERRABLE but INITIALLY IMMEDIATE. However, this may have performance implications compared to immediate uniqueness checking.

Limitations:

FOREIGN KEY constraints cannot reference DEFERRABLE columns because the referenced columns must be part of a non-deferrable unique or primary key constraint.

The above is the detailed content of When Are DEFERRABLE PRIMARY/UNIQUE Key Constraints Actually Enforced in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!