SQL CHECK Constraint

A CHECK constraint is a rule attached to a table that every row must satisfy before the database will accept it. Instead of relying on application code to make sure a price is positive, an age is realistic, or a status is one of a fixed set of values, you push that rule into the schema itself. It is then enforced no matter what writes the row — your application, a migration script, or someone typing directly into a SQL client. CHECK is one of the simplest and most effective tools for stopping bad data at the door.

Overview / How It Works

A CHECK constraint wraps a boolean expression around a column, or a combination of columns. Whenever a row is inserted or an existing row is updated, the database engine evaluates that expression using the row’s new values. If the expression evaluates to TRUE, the write proceeds. If it evaluates to FALSE, the engine immediately rejects the statement and raises a constraint violation error — no row is written, and if the statement affected multiple rows, none of them are committed.

There is a subtlety that trips up a lot of developers: SQL uses three-valued logic (TRUE, FALSE, and UNKNOWN). A CHECK constraint only blocks a row when the expression is definitively FALSE. If any operand involved is NULL, most comparisons evaluate to UNKNOWN, and UNKNOWN is treated the same as TRUE for constraint-checking purposes — the row is let through. This is why a CHECK constraint is never a substitute for NOT NULL; the two are independent constraints that are commonly used together.

You can define a CHECK constraint two ways:

Column-level — written directly after a single column’s type, and it can only reference that column.
Table-level — declared separately (usually at the end of the CREATE TABLE statement), which lets the expression reference multiple columns at once, such as ensuring a discount price never exceeds the regular price.

Under the hood, a CHECK constraint is purely a validation rule, not an index. It never speeds up a query, and the optimizer generally does not use it for query planning. Its entire job is to run at write time and refuse rows that break the rule. Compare that to PRIMARY KEY and UNIQUE, which build an index and enforce uniqueness, or FOREIGN KEY, which enforces that a value exists in another table. CHECK is the general-purpose tool for everything else: value ranges, allowed sets of strings, and cross-column relationships that don’t require looking at other tables.

Support varies slightly by database. SQLite, PostgreSQL, SQL Server, and MySQL 8.0.16+ all fully enforce CHECK constraints. Older MySQL versions (before 8.0.16) parsed the CHECK syntax but silently ignored it — a well-known trap for anyone assuming portability. Most engines also disallow subqueries or references to other tables inside a CHECK expression; the expression must be evaluable using only the row being written.

Syntax

-- Column-level CHECK constraint
 CREATE TABLE table_name (
     column1 datatype CHECK (condition),
     column2 datatype
 );

 -- Table-level CHECK constraint (can reference multiple columns)
 CREATE TABLE table_name (
     column1 datatype,
     column2 datatype,
     CONSTRAINT constraint_name CHECK (condition_involving_columns)
 );

Part	Meaning
`CHECK (condition)`	A boolean expression evaluated against each row’s values; rows where it is `FALSE` are rejected.
`CONSTRAINT constraint_name`	An optional name for the constraint. Naming it makes error messages clearer and lets you drop or alter it later by name in engines that support that.
Column-level placement	Written right after one column’s datatype; can only reference that single column.
Table-level placement	Written as its own clause in the table definition; can reference any combination of columns in the table.

Examples

Example 1: Enforcing positive numbers

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    price REAL CHECK (price > 0),
    stock_quantity INTEGER CHECK (stock_quantity >= 0)
);

INSERT INTO products (product_id, product_name, price, stock_quantity) VALUES
    (1, 'Wireless Mouse', 19.99, 150),
    (2, 'Mechanical Keyboard', 89.50, 75),
    (3, 'USB-C Cable', 7.25, 300);

SELECT * FROM products;

Result:

product_id	product_name	price	stock_quantity
1	Wireless Mouse	19.99	150
2	Mechanical Keyboard	89.5	75
3	USB-C Cable	7.25	300

Two column-level CHECK constraints guard this table: price must be strictly greater than 0, and stock_quantity can’t go negative. All three inserted rows satisfy both rules, so every insert succeeds and the table now holds three products.

Example 2: Restricting a column to a fixed set of values

CREATE TABLE employees_status (
    emp_id INTEGER PRIMARY KEY,
    emp_name TEXT NOT NULL,
    employment_status TEXT NOT NULL,
    CONSTRAINT chk_status CHECK (employment_status IN ('Active', 'On Leave', 'Terminated'))
);

INSERT INTO employees_status (emp_id, emp_name, employment_status) VALUES
    (1, 'Dana Cole', 'Active'),
    (2, 'Marcus Reyes', 'On Leave'),
    (3, 'Priya Nair', 'Active');

SELECT emp_name, employment_status FROM employees_status ORDER BY emp_name;

Result:

emp_name	employment_status
Dana Cole	Active
Marcus Reyes	On Leave
Priya Nair	Active

Here a named, table-level CHECK constraint (chk_status) acts like a lightweight enum, since plain SQL has no native enum type. Only the three listed strings are allowed in employment_status; any other text (a typo like 'Actve', for instance) would be rejected before it ever reached the table.

Example 3: A CHECK that compares two columns

CREATE TABLE catalog_items (
    item_id INTEGER PRIMARY KEY,
    item_name TEXT NOT NULL,
    price REAL NOT NULL,
    discount_price REAL,
    CHECK (discount_price IS NULL OR discount_price <= price)
);

INSERT INTO catalog_items (item_id, item_name, price, discount_price) VALUES
    (1, 'Desk Lamp', 40.00, 25.00),
    (2, 'Office Chair', 150.00, NULL),
    (3, 'Monitor Stand', 35.00, 30.00);

SELECT item_name, price, discount_price FROM catalog_items;

Result:

item_name	price	discount_price
Desk Lamp	40.0	25.0
Office Chair	150.0	NULL
Monitor Stand	35.0	30.0

This is a table-level constraint because it must see two columns at once: discount_price and price. The discount_price IS NULL OR ... guard means a product with no discount (a NULL value) is fine, but any product that does have a discount price must have it at or below the regular price. All three rows comply, so all three inserts succeed.

How It Works Step by Step / Under the Hood

When you run an INSERT (the same logic applies to UPDATE), the engine works through several stages in order:

Parse and validate the statement syntactically.
Build the candidate row image, filling in any columns you didn’t specify with their DEFAULT value or NULL.
Apply NOT NULL constraints — reject immediately if a NOT NULL column would receive NULL.
Evaluate every CHECK constraint on the table against the candidate row’s values. Each one must return TRUE or UNKNOWN to pass.
If any CHECK returns FALSE, abort the statement and raise a constraint failure — nothing is written, and the statement is rolled back as a unit.
If every CHECK passes, continue to UNIQUE and PRIMARY KEY checks (duplicate detection).
Evaluate FOREIGN KEY constraints, if enabled.
If every constraint passes, the row is written and the statement commits (or the surrounding transaction continues).

For a multi-row statement like INSERT ... SELECT or a bulk INSERT with several value lists, each candidate row is checked independently, but a single row that fails its CHECK aborts the whole statement by default — there’s no silent skip-and-continue behavior in standard SQL. This all-or-nothing behavior is what makes CHECK reliable: you never end up with a table that’s partially valid.

Common Mistakes

Mistake 1: Assuming CHECK also blocks NULL

CREATE TABLE subscriptions (
    sub_id INTEGER PRIMARY KEY,
    plan_type TEXT CHECK (plan_type IN ('Free', 'Pro', 'Enterprise'))
);

INSERT INTO subscriptions (sub_id, plan_type) VALUES (1, 'Pro');
INSERT INTO subscriptions (sub_id, plan_type) VALUES (2, NULL);

SELECT * FROM subscriptions;

Result: both inserts succeed, returning rows (1, 'Pro') and (2, NULL). It’s tempting to assume the second insert should fail since NULL isn’t in the allowed list, but it doesn’t — comparing NULL against the IN list produces UNKNOWN, not FALSE, and a CHECK only rejects rows where the expression is definitively FALSE. Fix: if the column must always hold a real value, add NOT NULL alongside the CHECK, e.g. plan_type TEXT NOT NULL CHECK (plan_type IN ('Free', 'Pro', 'Enterprise')).

Mistake 2: Inserting a value that violates the rule

CREATE TABLE inventory (
    id INTEGER PRIMARY KEY,
    item_name TEXT NOT NULL,
    quantity INTEGER CHECK (quantity >= 0)
);

INSERT INTO inventory (id, item_name, quantity) VALUES (1, 'Bluetooth Speaker', -5);

This fails with a constraint violation error (SQLite reports something like CHECK constraint failed: quantity >= 0) because -5 makes the expression evaluate to FALSE. No row is inserted. The fix is simply to only ever write values that satisfy the rule:

CREATE TABLE inventory (
    id INTEGER PRIMARY KEY,
    item_name TEXT NOT NULL,
    quantity INTEGER CHECK (quantity >= 0)
);

INSERT INTO inventory (id, item_name, quantity) VALUES (1, 'Bluetooth Speaker', 5);

SELECT * FROM inventory;

Result: one row, (1, 'Bluetooth Speaker', 5) — the insert now succeeds because 5 satisfies quantity >= 0. In real applications, a failed CHECK almost always means the value came from bad user input or a bug upstream; the constraint is doing its job by refusing to let it corrupt the table.

Mistake 3: Trying to enforce cross-table rules with CHECK

A CHECK expression can only see the row currently being written — it cannot query another table (most engines reject or refuse to reliably enforce a subquery inside CHECK). If your rule depends on data in a different table (for example, "the order total must match the sum of its line items"), CHECK is the wrong tool. Use a FOREIGN KEY for referential integrity, or a trigger for cross-table business rules.

Best Practices

Name your CHECK constraints with CONSTRAINT chk_name ... so error messages are readable and the constraint is easy to find or drop later.
Pair CHECK with NOT NULL whenever a column must always hold a valid, non-null value — remember that NULL always passes a CHECK on its own.
Keep expressions simple and self-contained; avoid subqueries or references to other tables, since most engines don’t support or reliably enforce them inside CHECK.
Use CHECK for domain, range, and enum-style rules; use FOREIGN KEY for integrity between tables.
Before adding a CHECK constraint to a table that already has rows, verify or clean up existing data — many engines validate all existing rows immediately and will refuse the change if any row currently violates the rule.
Test constraint behavior with NULL explicitly during development so you’re not surprised later by rows that slip through.
Don’t rely on CHECK alone for uniqueness or referential rules — it isn’t an index and can’t check other tables; pair it with UNIQUE, PRIMARY KEY, or FOREIGN KEY as appropriate.

Practice Exercises

Create a table named books with columns id, title, and pages, adding a CHECK constraint that requires pages to be greater than 0. Try inserting a book with pages set to 0 and confirm the database rejects it.
Create a table named students with a grade_level column restricted by a CHECK constraint to values between 1 and 12 (inclusive). Insert a row with grade_level set to NULL — is it accepted or rejected? Explain why in terms of three-valued logic.
Create a table named events with start_date and end_date columns, and add a table-level CHECK constraint ensuring end_date is never earlier than start_date. Test it with one valid row and one row where end_date comes before start_date.

Summary

CHECK constraints validate a boolean condition against every row before an INSERT or UPDATE is allowed to commit.
Column-level checks can only reference their own column; table-level checks can reference multiple columns at once.
NULL values pass a CHECK constraint unless a separate NOT NULL constraint is also applied — a CHECK only blocks rows where the expression is definitively FALSE.
CHECK is a pure validation rule; it builds no index and gives no query performance benefit.
Violating a CHECK constraint aborts the whole statement with an error — there is no partial or silently-skipped write.
Use CHECK for domain rules (ranges, enumerated values, cross-column comparisons); use FOREIGN KEY for integrity that spans tables.
Enforcement isn’t universal across every database version — notably, MySQL only started enforcing CHECK as of 8.0.16.

SQL CHECK Constraint

Overview / How It Works

Syntax

Examples

Example 1: Enforcing positive numbers

Example 2: Restricting a column to a fixed set of values

Example 3: A CHECK that compares two columns

How It Works Step by Step / Under the Hood

Common Mistakes

Mistake 1: Assuming CHECK also blocks NULL

Mistake 2: Inserting a value that violates the rule

Mistake 3: Trying to enforce cross-table rules with CHECK

Best Practices

Practice Exercises

Summary

Table of Contents