SQL Reference ยท Constraints and Data Types
SQL CHECK constraint
Reference for the SQL CHECK constraint: validate values with a condition, column and table checks, NULL behavior, naming, and dialect differences.
A CHECK constraint attaches a condition to a column or a table. Every row written by an INSERT or UPDATE must make the condition true, or the statement fails and nothing is stored. Typical uses are non-negative prices, allowed status values and consistent date ranges.
A check is evaluated for the new row only. It cannot normally look at other rows or other tables, so it validates the shape of one row, not rules like "total across the table". For those use unique constraints, foreign keys or triggers.
CREATE TABLE items ( price REAL NOT NULL CHECK (price >= 0), status TEXT CHECK (status IN ('new', 'sold')) );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
-- column constraint column_name data_type CHECK (condition) -- table constraint (may use several columns) [CONSTRAINT constraint_name] CHECK (condition)
| Part | Description |
|---|---|
condition |
Any boolean expression over the row's columns, using operators, IN, BETWEEN, LIKE, and deterministic functions. |
CONSTRAINT constraint_name |
Optional. A name makes error messages and later changes (DROP CONSTRAINT) much clearer. |
Rule: the row is accepted when the condition is TRUE or UNKNOWN. Only FALSE rejects it. A NULL value therefore passes CHECK (price >= 0), because NULL >= 0 is unknown.
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | Enforced always. A violation reports "CHECK constraint failed" with the constraint name or expression. Cannot add a check with ALTER TABLE. |
| PostgreSQL | Enforced. Add with ALTER TABLE ... ADD CONSTRAINT ... CHECK (...), optionally NOT VALID. |
| MySQL | Parsed but ignored before 8.0.16. Enforced since 8.0.16. |
| SQL Server | Enforced. Existing rows are checked when you add one, unless WITH NOCHECK. |
| Oracle | Enforced. Conditions cannot use subqueries or non-deterministic functions such as SYSDATE. |
-- PostgreSQL, MySQL 8.0.16+, SQL Server, Oracle ALTER TABLE products ADD CONSTRAINT products_price_not_negative CHECK (price >= 0);
Usage notes
- Add
NOT NULLtoo. BecauseNULLpasses a check,CHECK (price >= 0)does not stop a missing price. UseNOT NULL CHECK (...). - Prefer a lookup table for long lists.
IN ('a', 'b', 'c')is fine for three values, but a foreign key to a table of allowed values is easier to extend. - Keep checks simple and deterministic. A check that depends on the current date breaks old rows later and is rejected by some databases.
- Name your table checks, and use a naming pattern like
table_column_ruleso the error message tells you what to fix. - Checks guard all writers. Unlike application validation, they also catch scripts, imports and other tools that bypass your code.
- Cost: checks run on each write, but they are cheap compared to the I/O.
- Boolean columns:
CHECK (is_active IN (0, 1))is the usual guard in databases without a real boolean type.
Related
NOT NULL,DEFAULT,UNIQUE,CREATE TABLE,CREATE TRIGGER- Lessons: Constraints, Create table, Triggers