How to Fix PostgreSQL Error: Check Constraint Violated

The "Check constraint violated" error (also shown as "new row for relation violates check constraint") occurs when an INSERT or UPDATE operation attempts to store data that doesn't satisfy a CHECK constraint's boolean expression. CHECK constraints enforce business rules and data validation at the database level.

Impact

This error prevents data modifications that violate business rules, ensuring data quality and consistency. However, it can cause application errors if validation logic is missing or inconsistent between application and database layers.

Common Causes

Data violating defined business rules (e.g., price < 0)
Application validation not matching database constraints
Bulk data import with invalid values
Logic errors in UPDATE statements
Missing application-level validation
Changed business rules not reflected in constraints
Mathematical or logical expression errors

Troubleshooting and Resolution Steps

Identify the constraint and violation:

-- Error example: new row for relation "products" violates check constraint "products_price_check"
-- Detail: Failing row contains (...).

-- View table constraints
\d products

-- Query check constraints
SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'products'::regclass AND contype = 'c';

Common CHECK constraint patterns:

-- Positive values only
ALTER TABLE products ADD CONSTRAINT products_price_check
    CHECK (price > 0);

-- Range validation
ALTER TABLE employees ADD CONSTRAINT age_check
    CHECK (age BETWEEN 18 AND 100);

-- Enum-like validation
ALTER TABLE orders ADD CONSTRAINT status_check
    CHECK (status IN ('pending', 'processing', 'shipped', 'delivered'));

-- Multiple column comparison
ALTER TABLE events ADD CONSTRAINT date_check
    CHECK (end_date >= start_date);

-- Pattern matching
ALTER TABLE users ADD CONSTRAINT email_check
    CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Conditional constraints
ALTER TABLE products ADD CONSTRAINT discount_check
    CHECK (discount_price IS NULL OR discount_price < price);

Fix data to satisfy constraint:

-- WRONG: Negative price
INSERT INTO products (name, price) VALUES ('Widget', -10.00);

-- CORRECT
INSERT INTO products (name, price) VALUES ('Widget', 10.00);

-- UPDATE to fix existing data
UPDATE products SET price = ABS(price) WHERE price < 0;

Modify constraint if business rules changed:

-- Drop existing constraint
ALTER TABLE products DROP CONSTRAINT products_price_check;

-- Add new constraint with updated rules
ALTER TABLE products ADD CONSTRAINT products_price_check
    CHECK (price >= 0);  -- Changed from > to >= to allow free items

Add constraint to existing table safely:

-- Add constraint only if all data satisfies it
-- First, check for violations
SELECT * FROM products WHERE price <= 0;

-- Fix violations
UPDATE products SET price = 0.01 WHERE price <= 0;

-- Add constraint
ALTER TABLE products ADD CONSTRAINT products_price_check
    CHECK (price > 0);

-- Or add with NOT VALID to defer validation
ALTER TABLE products ADD CONSTRAINT products_price_check
    CHECK (price > 0) NOT VALID;

-- Validate later
ALTER TABLE products VALIDATE CONSTRAINT products_price_check;

Complex business logic constraints:

-- Percentage validation
ALTER TABLE discounts ADD CONSTRAINT percent_check
    CHECK (percentage >= 0 AND percentage <= 100);

-- Mutually exclusive fields
ALTER TABLE payments ADD CONSTRAINT payment_method_check
    CHECK (
        (credit_card IS NOT NULL AND bank_transfer IS NULL AND cash IS NULL) OR
        (credit_card IS NULL AND bank_transfer IS NOT NULL AND cash IS NULL) OR
        (credit_card IS NULL AND bank_transfer IS NULL AND cash IS NOT NULL)
    );

-- Conditional requirements
ALTER TABLE employees ADD CONSTRAINT manager_check
    CHECK (
        (is_manager = false) OR
        (is_manager = true AND years_experience >= 5)
    );

Use domains for reusable constraints:

-- Create domain with constraint
CREATE DOMAIN email AS VARCHAR(255)
    CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE DOMAIN positive_price AS NUMERIC(10,2)
    CHECK (VALUE > 0);

-- Use in table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email email NOT NULL,
    balance positive_price DEFAULT 0
);

Handle violations in application:

# Python with psycopg2
from psycopg2 import IntegrityError

try:
    cursor.execute(
        "INSERT INTO products (name, price) VALUES (%s, %s)",
        ('Widget', -10.00)
    )
    conn.commit()
except IntegrityError as e:
    conn.rollback()
    if 'check constraint' in str(e).lower():
        # Extract constraint name from error
        print("Data validation failed:", e)
        # Re-try with corrected value
        cursor.execute(
            "INSERT INTO products (name, price) VALUES (%s, %s)",
            ('Widget', 10.00)
        )
        conn.commit()

Temporarily disable constraint (not recommended):

-- Drop constraint
ALTER TABLE products DROP CONSTRAINT products_price_check;

-- Perform operations
-- ...

-- Re-add constraint
ALTER TABLE products ADD CONSTRAINT products_price_check
    CHECK (price > 0);

Find rows violating a potential constraint:

-- Before adding constraint, check what would fail
SELECT * FROM products WHERE price <= 0;

-- Check complex constraint
SELECT * FROM events WHERE end_date < start_date;

-- Check with explain
EXPLAIN SELECT * FROM products WHERE NOT (price > 0);

Additional Information

CHECK constraints are evaluated for each row
Constraints can reference multiple columns
NULL values pass most CHECK constraints (unless explicitly checked)
Check constraints can't use subqueries or reference other tables
Use foreign keys for referential integrity, CHECK for value validation
Complex business logic might be better in triggers or application code
Document constraint meanings for future developers

Frequently Asked Questions

Q: Can CHECK constraints reference other tables?
A: No, CHECK constraints can only reference columns in the same row. Use triggers or foreign keys for cross-table validation.

Q: Do CHECK constraints validate NULL values?
A: NULL typically passes CHECK constraints because NULL is "unknown". To prevent NULL, use column IS NOT NULL in the constraint.

Q: What's the difference between CHECK constraints and NOT NULL?
A: NOT NULL is a special case ensuring a column always has a value. CHECK constraints can enforce any boolean expression.

Q: Can I use functions in CHECK constraints?
A: Yes, but only immutable functions (functions that always return the same result for the same inputs). Avoid volatile functions.

Q: How do CHECK constraints affect performance?
A: They add minimal overhead as they're evaluated only during INSERT/UPDATE. The impact is usually negligible compared to the data quality benefits.

Q: Can I have multiple CHECK constraints on one column?
A: Yes, all constraints must be satisfied. However, combining them into one constraint is often clearer.

Q: What happens to CHECK constraints during bulk COPY operations?
A: COPY operations validate CHECK constraints for each row. Invalid rows will cause the operation to fail unless you handle errors appropriately.

Q: Should I use CHECK constraints or application validation?
A: Use both. Application validation provides immediate user feedback, while database constraints ensure data integrity regardless of which application accesses the database.