// PostgreSQL error reference

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
Error message
new row for relation "orders" violates check constraint "orders_qty_check"
DETAIL:  Failing row contains (12, -1).

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 CHECK constraint 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

  1. Read the constraint's condition.

    SELECT conname, pg_get_constraintdef(oid)
    FROM pg_constraint
    WHERE conrelid = 'orders'::regclass AND contype = 'c';
  2. Fix the data being written so it meets the condition.

  3. 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

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.

// fewer errors, faster fixes

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.