40002ERRORTier 2 — Caution✅ HIGH confidence

transaction integrity constraint violation

Category: Transaction RollbackVersions: All Postgres versions

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

1A DEFERRED constraint (NOT DEFERRABLE constraints checked at statement end) was violated and could not be resolved by COMMIT time
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

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

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

Fix 1: Resolve constraint violations within the transaction before committing

When using deferred constraints.

fix

-- 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 docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

🔧 Source ref: Class 40 — Transaction Rollback

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE for deferred constraint violations. Stable across versions.