CHECK Constraint
SQL · Working with Tables
Theory
A CHECK constraint is a rule, written as a condition, that every row must satisfy. salary DECIMAL(10,2) CHECK (salary > 0) rejects zero or negative salaries; CHECK (status IN ('active', 'left')) limits a column to known values; a table-level CHECK (end_date >= start_date) compares two columns of the same row.
The condition is tested on every INSERT and UPDATE, and the statement fails if the result is FALSE. A subtle rule: a CHECK passes when the result is UNKNOWN, so a NULL salary is accepted by CHECK (salary > 0). Add NOT NULL if the value is required. CHECK can only look at the current row; rules that involve other rows or tables need foreign keys, triggers or application logic. Name constraints (CONSTRAINT chk_salary CHECK (...)) to get clearer error messages. Older MySQL versions parsed CHECK but ignored it.
Why we use it
Some values are simply impossible: negative quantities, percentages above 100, end dates before start dates. CHECK blocks them inside the database so no application or manual fix can slip bad data in.
When to use it
Use CHECK for simple, row-level business rules on values. For lists of allowed values that change often, a lookup table with a foreign key is easier to maintain. For cross-row rules, use triggers or application code.
How it works
- INSERT or UPDATE a row
- Evaluate the CHECK condition for that row
- Is the result FALSE? Yes: Reject the statement. No: TRUE or UNKNOWN (NULL): accept the row.
- Repeat for every row the statement touches
- Only rule-abiding rows are stored
Syntax
column datatype CHECK (condition)Example
CREATE TABLE employees (
salary DECIMAL(10,2) CHECK (salary > 0)
);Key points
- Rejects any insert/update where the condition is FALSE
- Can reference multiple columns of the same row in one CHECK
- NULL makes the condition UNKNOWN, which passes; add NOT NULL if required
- Cannot look at other rows or tables
- Name constraints for clearer error messages