PostgreSQL 23514 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 23514: check_violation — What It Means and How to Fix It

PostgreSQL error code 23514, known as check_violation, occurs when an INSERT or UPDATE operation attempts to store data that fails a CHECK constraint defined on a table. These constraints enforce business rules at the database level, acting as a final safety net for data integrity. When a violation is detected, PostgreSQL immediately aborts the statement and rolls back the current transaction.

Top 3 Causes

1. Value Outside an Allowed Range

The most common cause is inserting a numeric or date value that falls outside the defined boundary, such as a negative price or an invalid age.

-- Table with a range CHECK constraint
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) CHECK (price > 0),
    stock INTEGER CHECK (stock >= 0)
);

-- This will trigger error 23514
INSERT INTO products (name, price, stock)
VALUES ('Widget', -50.00, 100);
-- ERROR: new row for relation "products" violates check constraint "products_price_check"

-- Fix: insert a valid value
INSERT INTO products (name, price, stock)
VALUES ('Widget', 50.00, 100);

2. Multi-Column CHECK Constraint Violated

When a CHECK constraint spans multiple columns (e.g., ensuring start_date < end_date), it is easy to violate when one value is updated independently without considering the other.

-- Table with a multi-column CHECK constraint
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(200) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    CONSTRAINT chk_event_dates CHECK (start_date < end_date)
);

-- This will fail because end_date is before start_date
INSERT INTO events (event_name, start_date, end_date)
VALUES ('Summit', '2024-12-01', '2024-06-01');
-- ERROR: new row for relation "events" violates check constraint "chk_event_dates"

-- Fix: correct the date order
INSERT INTO events (event_name, start_date, end_date)
VALUES ('Summit', '2024-06-01', '2024-12-01');

3. Value Not in an Allowed Whitelist

A CHECK constraint that enforces an allowed set of string values (acting like a lightweight enum) will reject any value not in that list. This is especially common when new status values are added to application code without updating the database constraint.

-- Table with a whitelist CHECK constraint
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20) NOT NULL,
    CONSTRAINT chk_order_status
        CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

-- Fails: 'archived' is not in the allowed list
INSERT INTO orders (status) VALUES ('archived');
-- ERROR: new row for relation "orders" violates check constraint "chk_order_status"

-- Fix: update the constraint to include new values
ALTER TABLE orders DROP CONSTRAINT chk_order_status;
ALTER TABLE orders ADD CONSTRAINT chk_order_status
    CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled', 'archived'));

Quick Fix Solutions

Step 1 — Identify the violated constraint:

-- Find all CHECK constraints on a table
SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'your_table'::regclass
  AND contype = 'c';

Step 2 — Modify or drop and recreate the constraint if business rules have changed:

-- Safely add a new CHECK constraint on a large table without locking
ALTER TABLE products
    ADD CONSTRAINT chk_price_non_negative CHECK (price >= 0) NOT VALID;

-- Then validate existing data in the background
ALTER TABLE products VALIDATE CONSTRAINT chk_price_non_negative;

Prevention Tips

1. Always name your CHECK constraints explicitly.
Using CONSTRAINT chk_meaningful_name CHECK (...) makes error messages instantly readable and simplifies future ALTER TABLE operations. Anonymous constraints generate unpredictable system names that are hard to manage in migrations.

2. Validate data at the application layer before it reaches the database.
Treat the CHECK constraint as a last line of defense, not the primary validator. Pre-validate inputs in your service or API layer to return user-friendly error messages and avoid unnecessary database round-trips. When migrating large datasets, use the NOT VALID + VALIDATE CONSTRAINT pattern to avoid table-level locks.

Related Errors

Code	Name	Description
23502	`not_null_violation`	NULL inserted into a NOT NULL column
23503	`foreign_key_violation`	Referential integrity broken
23505	`unique_violation`	Duplicate value in a UNIQUE column
23P01	`exclusion_violation`	EXCLUDE constraint violated

All of these belong to PostgreSQL Class 23 — Integrity Constraint Violation and serve as the database's core data quality enforcement layer.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community