Error
Error Code:
3812
MySQL Error 3812: Non-Boolean Check Constraint Expression
Description
This error occurs when a `CHECK` constraint is defined with an expression that does not evaluate to a boolean value (TRUE, FALSE, or NULL). MySQL requires `CHECK` constraints to use expressions that produce a boolean result to determine if the constraint is satisfied.
Error Message
An expression of non-boolean type specified to a check constraint '%s'.
Known Causes
3 known causesMissing Comparison Operator
An expression uses a column name or a value directly without a comparison operator, such as `=`, `>`, or `LIKE`, expecting it to implicitly evaluate to a boolean.
Non-Boolean Function Result
A function returning a numeric, string, or date/time type is used as the sole condition, instead of its result being part of a boolean comparison.
Arithmetic Expression as Condition
An arithmetic expression, like `(column1 + column2)`, is specified directly without being compared to a value or another expression that yields a boolean.
Solutions
3 solutions available1. Correct Check Constraint Expression Type easy
Ensure the expression used in the CHECK constraint evaluates to a boolean value.
1
Review the CHECK constraint definition for the table that is causing the error. Identify the expression used within the `CHECK (...)` clause.
2
Modify the expression so that it always returns a boolean value (TRUE or FALSE). This typically involves using comparison operators (>, <, =, !=, >=, <=), logical operators (AND, OR, NOT), or functions that return boolean values.
ALTER TABLE your_table_name
ADD CONSTRAINT constraint_name CHECK (your_boolean_expression);3
For example, if your constraint was `CHECK (age)` where `age` is an integer column, it would fail. Change it to `CHECK (age > 0)`.
ALTER TABLE users
ADD CONSTRAINT age_positive CHECK (age > 0);2. Remove or Recreate the Problematic Constraint medium
Drop the invalid CHECK constraint and recreate it with a valid boolean expression.
1
Identify the name of the CHECK constraint causing the error. You can find this in the error message (`'%s'`) or by inspecting the table's schema.
SHOW CREATE TABLE your_table_name;2
Drop the existing CHECK constraint.
ALTER TABLE your_table_name
DROP CONSTRAINT constraint_name;3
Recreate the CHECK constraint with a corrected, boolean-evaluating expression. Refer to Solution 1 for guidance on creating valid expressions.
ALTER TABLE your_table_name
ADD CONSTRAINT new_constraint_name CHECK (your_correct_boolean_expression);3. Temporarily Disable Constraint Checks (with caution) medium
Disable constraint checking for a specific operation or session, then re-enable it.
1
To disable CHECK constraint enforcement for the current session, execute the following command.
SET session sql_require_primary_key = 0;2
Perform the operation that was causing the error (e.g., `ALTER TABLE` or `INSERT`/`UPDATE` that triggered the constraint).
3
Crucially, re-enable constraint checking for the session. If you are using older MySQL versions that do not support `sql_require_primary_key` in this manner for CHECK constraints, you might need to drop and recreate the constraint as per Solution 2.
SET session sql_require_primary_key = 1;