new row violates check constraint

This error occurs when you insert or update data that doesn't satisfy a CHECK constraint on the table.

The new row violates check constraint error means your data failed a validation rule.

Understanding the Error

ERROR: new row for relation "products" violates check constraint "products_price_check"
DETAIL: Failing row contains (1, Widget, -5.00).

The value doesn't meet the CHECK constraint's condition.

Common Causes

1. Invalid Value Range

SQL

-- CHECK (price > 0)
INSERT INTO products (name, price) VALUES ('Widget', -5.00);  -- Error!

2. Invalid Status Values

SQL

-- CHECK (status IN ('active', 'inactive', 'pending'))
UPDATE users SET status = 'deleted';  -- Error!

3. Invalid Age or Date Range

SQL

-- CHECK (age >= 0 AND age <= 150)
INSERT INTO persons (name, age) VALUES ('John', -1);  -- Error!

How to Fix It

Solution 1: Use Valid Values

SQL

-- Provide value that satisfies the constraint
INSERT INTO products (name, price) VALUES ('Widget', 5.00);  -- price > 0

Solution 2: Find the Constraint Definition

SQL

-- View all check constraints on a table
SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'products'::regclass
AND contype = 'c';

Solution 3: Modify the Constraint

SQL

-- Drop the old constraint
ALTER TABLE products DROP CONSTRAINT products_price_check;

-- Add new constraint with different rules
ALTER TABLE products ADD CONSTRAINT products_price_check CHECK (price >= 0);

Solution 4: Validate Before Insert

JAVASCRIPT

function addProduct(name, price) {
  if (price <= 0) {
    throw new Error('Price must be positive');
  }
  return db.query('INSERT INTO products (name, price) VALUES ($1, $2)', [name, price]);
}

Solution 5: Use ENUM Type Instead

SQL

-- Instead of CHECK constraint for fixed values
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'pending');

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  status user_status NOT NULL DEFAULT 'pending'
);

Common CHECK Constraints

SQL

-- Positive numbers
CHECK (quantity > 0)

-- Range constraint
CHECK (percentage BETWEEN 0 AND 100)

-- Status values
CHECK (status IN ('active', 'inactive'))

-- Date validation
CHECK (end_date > start_date)

-- Email format (basic)
CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')

Best Practices

Document constraints clearly for developers
Validate in application before database insert
Use meaningful constraint names for better error messages
Consider ENUM for fixed value sets
Test edge cases when defining constraints