📋

Description

Error 23514, 'check violation', indicates that an `INSERT` or `UPDATE` operation failed because the data being entered violates a `CHECK` constraint defined on one of the table columns. This constraint ensures that all values in a column satisfy a specific condition, preventing invalid data from being stored in the database.

💬

Error Message

check violation

🔍

Known Causes

3 known causes

⚠️

Invalid Data Value

An attempt was made to insert or update a column with a value that does not conform to the predefined conditions of a `CHECK` constraint, such as a number outside a valid range or text not matching a pattern.

⚠️

Application Logic Error

The application's logic generating the data for insertion or update does not correctly validate the data against the database's `CHECK` constraints, leading to a violation when the statement executes.

⚠️

Direct Data Entry Error

When manually inserting or updating data via SQL clients, a user might inadvertently provide a value that violates an existing `CHECK` constraint, leading to this error.

🛠️

Solutions

5 solutions available

1. Provide Valid Data easy

Ensure data meets CHECK constraint requirements

1

View constraint definition

SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'your_table'::regclass AND contype = 'c';

2

Fix data to meet constraint

-- If constraint is: CHECK (age >= 0 AND age <= 150)
-- Wrong: INSERT INTO users (age) VALUES (-5);
-- Right: INSERT INTO users (age) VALUES (25);

2. Modify CHECK Constraint medium

Change constraint to allow your data

1

Drop existing constraint

ALTER TABLE users DROP CONSTRAINT users_age_check;

2

Add new constraint with updated rules

ALTER TABLE users ADD CONSTRAINT users_age_check 
  CHECK (age >= 0 AND age <= 200);

3. Handle Enum-Style Constraints easy

Use valid values from allowed list

1

Check allowed values

-- If constraint is: CHECK (status IN ('active', 'inactive', 'pending'))
SELECT pg_get_constraintdef(oid)
FROM pg_constraint WHERE conname = 'users_status_check';

2

Use valid value

INSERT INTO users (status) VALUES ('active');  -- Valid
-- NOT: VALUES ('enabled');  -- Invalid

3

Or add new value to constraint

ALTER TABLE users DROP CONSTRAINT users_status_check;
ALTER TABLE users ADD CONSTRAINT users_status_check
  CHECK (status IN ('active', 'inactive', 'pending', 'enabled'));

4. Temporarily Disable Constraint advanced

For data migration only

1

Disable constraint validation

ALTER TABLE users ALTER CONSTRAINT users_age_check NOT VALID;

2

Or use session_replication_role

SET session_replication_role = 'replica';  -- Disables triggers/constraints
-- Do your inserts
SET session_replication_role = 'origin';  -- Re-enable

-- Then validate existing data:
ALTER TABLE users VALIDATE CONSTRAINT users_age_check;

5. Add Constraint with NOT VALID medium

Add constraint without validating existing data

1

Add constraint for new data only

ALTER TABLE users ADD CONSTRAINT users_age_check
  CHECK (age >= 0) NOT VALID;

2

Validate later after cleaning data

-- Fix invalid data first
UPDATE users SET age = 0 WHERE age < 0;

-- Then validate
ALTER TABLE users VALIDATE CONSTRAINT users_age_check;

🔗

Related Errors

5 related errors

PostgreSQL Error 23514: Check Constraint Violation