Schema & Design
CHECK Constraints
A CHECK constraint is a declarative rule that restricts the values a column or a group of columns may hold in a table.
Overview
A CHECK constraint is a declarative rule that restricts the values a column or a group of columns may hold in a table. The database enforces the rule on every INSERT and UPDATE, rejecting any row that makes the search condition evaluate to FALSE or UNKNOWN. Because the condition is stored with the table definition, the rule applies uniformly to all clients and cannot be bypassed by application code. CHECK constraints are part of SQL-92 and are supported by essentially every major relational database, though the exact expression grammar varies. Typical uses include enforcing positive quantities, limiting a status column to a fixed set of strings, or requiring that one date precedes another. A CHECK constraint is not a substitute for a foreign key or a unique constraint; it only validates expressions over the row's own columns, with some engines also permitting deterministic functions. Unlike triggers, CHECK constraints are evaluated as part of the statement that modifies data, so violations surface as ordinary constraint errors rather than side effects.
Syntax and usage
The general form is CHECK (search_condition), attached either to a column or to the table. Column-level syntax appears in a column definition, for example quantity INTEGER CHECK (quantity > 0). Table-level syntax lists the constraint after all columns, for example CONSTRAINT chk_dates CHECK (start_date <= end_date). Named constraints are preferable because they produce readable error messages and can be dropped or altered by name later. In CREATE TABLE you may write CONSTRAINT name CHECK (...), and in ALTER TABLE you may add one with ALTER TABLE t ADD CONSTRAINT name CHECK (...). Most systems also accept an unnamed inline form, which the engine assigns a generated name. The search condition may reference any column of the same row and may combine comparisons with AND, OR, NOT, IN, BETWEEN, and CASE. It must be deterministic and must not contain subqueries in standard SQL, although a few products relax this. Whether NULL passes depends on the three-valued logic of the condition.
How it works
The database parses the search condition when the constraint is created and stores it in the catalog alongside the table metadata. At execution time, the optimizer may fold the condition into the plan for INSERT, UPDATE, and sometimes MERGE statements, so each affected row is tested before it is written. If the condition evaluates to TRUE, the row is accepted; if it evaluates to FALSE, the statement fails with a constraint violation and, in a transactional engine, the whole statement is rolled back. If it evaluates to UNKNOWN, typically because a referenced column is NULL, the row is accepted, since only FALSE is treated as a violation. This behavior means a constraint such as CHECK (price > 0) does not reject NULL prices; add NOT NULL or write CHECK (price IS NULL OR price > 0) when NULLs must be excluded. Adding a CHECK constraint to an existing table usually requires validating current rows, which may scan the table; some products allow the constraint to be added as not validated and enforced only for new data.
Examples
A column-level constraint limiting a status value: CREATE TABLE orders (id INTEGER PRIMARY KEY, status VARCHAR(20) CHECK (status IN ('new','paid','shipped','closed')));. A table-level constraint comparing two columns: CREATE TABLE bookings (id INTEGER PRIMARY KEY, start_date DATE NOT NULL, end_date DATE NOT NULL, CONSTRAINT chk_period CHECK (end_date >= start_date));. Combining several rules in one named constraint: ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price >= 0 AND discount BETWEEN 0 AND price);. A constraint that explicitly permits NULL while rejecting non-positive values: CHECK (quantity IS NULL OR quantity > 0). To remove a constraint, use ALTER TABLE products DROP CONSTRAINT chk_price; in PostgreSQL, SQL Server, and Oracle, while MySQL uses ALTER TABLE products DROP CHECK chk_price;. To inspect existing constraints, query the information schema, for example SELECT constraint_name, check_clause FROM information_schema.check_constraints WHERE constraint_schema = 'public';, though column names and availability differ across products.
Common pitfalls
The most frequent mistake is assuming a CHECK constraint rejects NULL. Because the condition is UNKNOWN for NULL operands, the row is accepted unless the expression explicitly handles NULL. Another pitfall is referencing other rows or other tables; standard CHECK constraints are row-local and cannot express referential rules, which belong to foreign keys. Some developers write conditions that depend on non-deterministic functions such as the current timestamp, which most engines reject at creation time. Cross-column checks can also surprise users when one column is updated alone, since the whole row is re-evaluated. In MySQL before version 8.0.16, CHECK clauses were parsed but ignored, so rules silently did nothing; this is a compatibility trap when migrating. Finally, adding a constraint to a large table may lock or scan it, and dropping a constraint by a generated name is awkward, which is why naming constraints explicitly is recommended.
Best practices
Name every CHECK constraint with a consistent prefix such as chk_ followed by the table and column, so violations are easy to trace and the constraint can be altered by name. Keep each constraint focused on one logical rule; several small named constraints give clearer errors than one large compound expression. Always decide explicitly how NULL should behave and encode that decision, for example with IS NULL OR ... when NULL is allowed. Prefer CHECK constraints over application-level validation for invariants that must hold for every writer, and prefer them over triggers when the rule is purely row-local, since they are simpler and more portable. Test constraints with representative INSERT and UPDATE statements, including boundary values and NULLs. When adding a constraint to an existing table, validate the data first with a SELECT that finds violating rows, then add the constraint, optionally using a NOT VALID or equivalent clause where the product supports it.
Frequently asked questions
Does a CHECK constraint reject NULL values? No. A NULL makes most comparisons evaluate to UNKNOWN, and only FALSE is treated as a violation, so the row is accepted. Add NOT NULL or write the condition to handle NULL explicitly if NULLs should be rejected. Can a CHECK constraint reference another table? In standard SQL, no; the condition must be evaluated from the current row alone. Rules that span tables are expressed with foreign keys or, where available, triggers or assertions. Can I add a CHECK constraint to a table that already contains data? Yes, but the database normally validates existing rows, which may take time on large tables. Some products let you add the constraint as not validated so it applies only to future changes; you can validate it later once the data is corrected.