ClickHouse DB::Exception: Violated constraint

The "DB::Exception: Violated constraint" error in ClickHouse occurs when an INSERT attempts to write data that does not satisfy a CHECK constraint defined on the table. The VIOLATED_CONSTRAINT error code is raised when one or more rows in the insert batch fail the constraint's boolean expression.

Impact

The entire INSERT batch is rejected and no rows are committed. This protects data quality by preventing invalid data from entering the table, but it can block data pipelines if upstream data quality is not controlled. The table and its existing data remain unaffected.

Common Causes

1. The source data contains values outside the expected range (e.g., negative values where only positive are allowed)
2. Null values are being inserted into a column with a non-null constraint expression
3. Business logic violations such as an end date before a start date
4. A schema migration added a new constraint that existing ETL pipelines do not account for
5. Data type overflow — a value fits the column type but violates a range constraint
6. The constraint was defined with an incorrect expression that rejects valid data

Troubleshooting and Resolution Steps

1. Check which constraints are defined on the table by inspecting its definition:

   SHOW CREATE TABLE your_database.your_table;
   

   Look for CONSTRAINT name CHECK expr clauses. ClickHouse does not expose a dedicated system table for constraints, so SHOW CREATE TABLE is the way to review them.

2. Identify which rows violate the constraint by testing the condition against your data:

   -- If the constraint is: CONSTRAINT check_positive CHECK value > 0
   SELECT *
   FROM your_staging_table
   WHERE NOT (value > 0)
   LIMIT 20;
   

3. Fix the data before inserting, either by filtering or correcting:

   -- Filter out invalid rows
   INSERT INTO target_table
   SELECT * FROM staging_table
   WHERE value > 0;
   
   -- Or fix the data
   INSERT INTO target_table
   SELECT id, greatest(value, 0) AS value
   FROM staging_table;
   

4. If the constraint itself is wrong, modify or remove it:

   -- Drop a constraint
   ALTER TABLE your_table DROP CONSTRAINT check_positive;
   
   -- Add a corrected constraint
   ALTER TABLE your_table ADD CONSTRAINT check_positive CHECK value >= 0;
   

5. To see which specific constraint was violated (when multiple constraints exist), check the error message — it typically names the constraint:

   DB::Exception: Violated constraint check_positive in table your_table
   

Best Practices

• Define CHECK constraints for critical data quality rules to prevent bad data from entering ClickHouse.
• Name constraints descriptively so that error messages are easy to understand (e.g., check_end_after_start instead of c1).
• Test constraints against sample data before deploying them to production tables.
• Include constraint validation in your ETL pipeline to catch violations before they reach ClickHouse, providing better error messages to data producers.
• Use input_format_allow_errors_num cautiously — it does not bypass constraint checks.

Frequently Asked Questions

Q: Do CHECK constraints slow down inserts?
A: Minimally. The constraint expression is evaluated for each row in the insert batch. For simple expressions (comparisons, null checks), the overhead is negligible. Complex expressions with subqueries or heavy computation can have a measurable impact.

Q: Can I disable constraint checking temporarily?
A: There is no setting to temporarily bypass CHECK constraints. If you need to insert data that violates a constraint, you must drop the constraint, insert the data, and re-add the constraint. This approach should be used with caution.

Q: Are CHECK constraints enforced on existing data when I add them?
A: No. Adding a constraint only validates future inserts. Existing data that violates the new constraint remains in the table. To validate existing data, run a SELECT query with the constraint's condition.