Skip to content
Tutorials Center
My learning

Topics

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.

SQL
CREATE TABLE items (
  price REAL NOT NULL CHECK (price >= 0),
  status TEXT CHECK (status IN ('new', 'sold'))
);

Try it

SQL The shop's own checks (fails on purpose at the end) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Column checks, a table check, and NULL passing a check (fails on purpose at the end) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
-- 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.
SQL
-- PostgreSQL, MySQL 8.0.16+, SQL Server, Oracle
ALTER TABLE products
  ADD CONSTRAINT products_price_not_negative CHECK (price >= 0);

Usage notes

  • Add NOT NULL too. Because NULL passes a check, CHECK (price >= 0) does not stop a missing price. Use NOT 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_rule so 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.