PostgreSQL 23514: check constraint violation
A row failed a CHECK constraint on the table, or a domain's check, so the INSERT or UPDATE was rejected.
- SQLSTATE
23514- Name
check_violation- Category
- Integrity Constraint Violation
What it means
A CHECK constraint is a condition every row must satisfy, such as qty > 0. 23514 means the new or updated row made the condition false. A condition that evaluates to NULL passes, so checks don't reject NULL values on their own.
The message names the constraint; \d table_name in psql or pg_get_constraintdef shows its condition. Adding a check to a table with existing bad rows fails the same way, with check constraint ... of relation ... is violated by some row.
Common causes
- Data outside the allowed range or set, for example a negative quantity or an unknown status.
- Application validation that differs from the database's rules.
- Adding a
CHECKconstraint to a table that already has rows breaking it. - A domain type with a
CHECK(value for domain ... violates check constraint).
How to fix it
-
Read the constraint's condition.
SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'orders'::regclass AND contype = 'c';
-
Fix the data being written so it meets the condition.
-
To add a check to a table with old bad rows, add it
NOT VALID(enforced for new rows only), clean up, then validate.ALTER TABLE orders ADD CONSTRAINT orders_qty_check CHECK (qty > 0) NOT VALID; UPDATE orders SET qty = 1 WHERE qty <= 0; ALTER TABLE orders VALIDATE CONSTRAINT orders_qty_check;
Reproduce it safely
CREATE TEMP TABLE o (qty int CHECK (qty > 0)); INSERT INTO o VALUES (-1); -- ERROR: new row for relation "o" violates check constraint "o_qty_check"
Related errors
23502null value violates not-null constraint23505duplicate key violates unique constraint23P01exclusion constraint violation3819MySQL: Check constraint is violatedSQLITE_CONSTRAINT_CHECKSQLite: CHECK constraint failed
More integrity constraint violation errors
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.