PostgreSQL Transaction Integrity Constraint Violation (SQLSTATE 40002)

PostgreSQL raises ERROR: transaction_integrity_constraint_violation with SQLSTATE 40002 when a deferred constraint check fails at transaction commit time. The error message typically appears as:

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
SQLSTATE: 40002

This belongs to error class 40 — Transaction Rollback — meaning the entire transaction has already been rolled back by the time you receive it.

What This Error Means

SQLSTATE class 40 covers conditions that cause the current transaction to be automatically rolled back. SQLSTATE 40002 specifically designates a transaction integrity constraint violation, which is the subclass for constraint failures that are detected at the end of a transaction rather than at the point the offending statement ran.

In PostgreSQL, constraints can be declared as DEFERRABLE and set to INITIALLY DEFERRED. When a constraint is deferred, PostgreSQL postpones checking it until the transaction issues a COMMIT. If the constraint is still violated at that point, PostgreSQL raises SQLSTATE 40002 and rolls back the whole transaction. The connection remains open and usable after the rollback; the transaction simply never committed.

This is distinct from the more common 23xxx class (Integrity Constraint Violation), where a constraint is checked immediately after each statement. With 40002, the row data was accepted into the transaction's working set but ultimately rejected at commit — which is why the error originates from COMMIT, not from the INSERT or UPDATE that introduced the bad data.

Common Causes

1. A DEFERRABLE INITIALLY DEFERRED foreign key constraint is violated by the end of the transaction. For example, inserting a child row that references a parent row you intended to insert later in the same transaction, but the parent insert was never executed or was rolled back to a savepoint.

2. Explicit use of SET CONSTRAINTS ALL DEFERRED within a transaction, which defers all deferrable constraints to commit time. If any of those constraints remain unsatisfied at COMMIT, SQLSTATE 40002 is raised.

3. A DEFERRABLE INITIALLY IMMEDIATE constraint was switched to deferred mid-transaction via SET CONSTRAINTS <name> DEFERRED, and the constraint condition is violated when the transaction commits.

4. Application logic that relies on ordering within a transaction. Bulk loaders, import scripts, or ORM batch-flush operations sometimes write rows out of dependency order, expecting foreign key checks to be deferred, but a constraint is not actually declared DEFERRABLE.

How to Fix transaction_integrity_constraint_violation

1. Ensure the referenced parent row is committed before deferral ends. If you are loading data in bulk across related tables, either insert parent rows first within the same transaction or verify the parent rows already exist.

   BEGIN;
   -- Insert parent first
   INSERT INTO customers (id, name) VALUES (42, 'Acme Corp');
   -- Now insert the child row that references it
   INSERT INTO orders (id, customer_id, total) VALUES (1001, 42, 299.99);
   COMMIT;
   

2. Review which constraints are actually declared DEFERRABLE. Query the catalog to confirm your expectations match reality:

   SELECT conname, contype, condeferrable, condeferred
   FROM pg_constraint
   WHERE conrelid = 'your_table'::regclass;
   

   If a constraint is not deferrable but your code calls SET CONSTRAINTS ALL DEFERRED, the constraint is still checked immediately and the error will appear at the offending statement (SQLSTATE 23xxx), not at commit. If it is deferrable and deferred, failures surface as 40002.

3. Remove or narrow the scope of SET CONSTRAINTS ... DEFERRED if it was added as a workaround without understanding the constraint graph. Prefer inserting rows in dependency order so deferral is unnecessary.

4. Wrap the transaction in proper error handling. Because 40002 always results in a rolled-back transaction, any retry logic must restart the entire transaction — partial retries or savepoints cannot recover from this error.

   # Python / psycopg2 example
   while retries > 0:
       try:
           with conn.transaction():
               insert_parent(cursor)
               insert_child(cursor)
           break
       except psycopg2.errors.TransactionIntegrityConstraintViolation:
           retries -= 1
           # Re-examine data before retrying
   

5. Check for bugs in deferred-constraint-aware migration scripts. Tools that disable triggers or use SET CONSTRAINTS ALL DEFERRED during migrations can leave a database in a state where constraints pass during the migration but fail once normal operations resume with different deferral settings.

Additional Information

• SQLSTATE 40002 belongs to class 40 (Transaction Rollback). Related codes in the same class include 40001 (serialization_failure, raised by serializable transaction conflicts) and 40003 (statement_completion_unknown).
• The distinction between 23xxx and 40002 matters for client-side error handling: 23xxx aborts only the current statement (the transaction can continue with a ROLLBACK TO SAVEPOINT), while 40002 means the entire transaction has already been rolled back and must be retried from the start.
• Most PostgreSQL drivers expose this as a named exception. In psycopg2/psycopg3 it is psycopg2.errors.TransactionIntegrityConstraintViolation; in asyncpg it is accessible via asyncpg.exceptions.TransactionRollbackError with the SQLSTATE checked explicitly.
• ORMs that batch-flush writes (Django ORM with bulk_create, SQLAlchemy with flush()) may surface this error at the ORM flush/commit call rather than at the individual model save, making the root-cause row harder to identify from the stack trace alone.
• PostgreSQL's pg_constraint.condeferrable and pg_constraint.condeferred columns in the system catalog are the authoritative source for determining whether a constraint can produce SQLSTATE 40002.

Frequently Asked Questions

Why does the error come from COMMIT rather than from my INSERT or UPDATE? Because the constraint is declared DEFERRABLE INITIALLY DEFERRED (or was explicitly deferred with SET CONSTRAINTS). PostgreSQL queues the constraint check and only runs it when the transaction is about to commit. At that point, if any violation exists, the commit is refused, the transaction is rolled back, and SQLSTATE 40002 is returned.

Can I use a savepoint to recover from SQLSTATE 40002? No. SQLSTATE 40002 is a transaction-rollback class error. By the time your application receives it, PostgreSQL has already rolled back the entire transaction automatically. Savepoints only help with statement-level errors (class 23xxx). You must restart the full transaction.

How is 40002 different from 23xxx integrity constraint violations? Class 23xxx errors (e.g., 23503 foreign_key_violation, 23505 unique_violation) are raised immediately when a violating statement executes, while the transaction is still live. Class 40002 is raised only at commit time for deferred constraints. The practical difference is that 23xxx still gives you a live transaction you can roll back gracefully, whereas 40002 means the transaction is already gone.

How do I find out which row caused the violation? Enable log_min_messages = debug1 or check pg_last_error_statement() in your session context. For foreign key violations, the error detail line (e.g., DETAIL: Key (customer_id)=(42) is not present in table "customers".) is usually present in the server log even when the client only sees the top-level 40002 error. Some drivers suppress the detail field — check the raw server logs rather than relying solely on the exception message in application code.