EC
40002
PostgreSQLERRORNotableTransaction RollbackHIGH confidence

transaction integrity constraint violation

What this means

SQLSTATE 40002 is raised when a transaction is rolled back because deferred integrity constraints could not be satisfied at COMMIT time. Deferred constraints (CHECK, FOREIGN KEY, UNIQUE) are validated at the end of the transaction.

Why it happens
  1. 1A DEFERRED constraint (NOT DEFERRABLE constraints checked at statement end) was violated and could not be resolved by COMMIT time
  2. 2A deferred foreign key, unique, or check constraint finds violations when the transaction commits
How to reproduce

Deferred constraint violation at COMMIT.

trigger — this will error
trigger — this will error
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
DELETE FROM departments WHERE id = 1; -- employees still reference it
-- No intermediate error — deferred until COMMIT
COMMIT; -- ERROR 40002: FK constraint violated

expected output

ERROR:  insert or update on table "employees" violates foreign key constraint

Fix

Resolve constraint violations within the transaction before committing

WHEN When using deferred constraints.

Resolve constraint violations within the transaction before committing
-- Fix referential integrity before COMMIT:
UPDATE employees SET department_id = 2 WHERE department_id = 1;
DELETE FROM departments WHERE id = 1;
COMMIT;

Why this works

Deferred constraints are checked at COMMIT. Ensure all referential integrity is maintained within the transaction before committing.

Sources
Official documentation ↗

Class 40 — Transaction Rollback

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors