CHECK CONSTRAINTDDL
Buy Me A Coffee

A constraint that enforces a boolean condition on column values at the row level — the database rejects any INSERT or UPDATE that would violate it.

A user-defined condition that each row must satisfy, such as price > 0 or status IN (active, pending). Enforces business rules at the database level. Not supported by MySQL InnoDB engine before 8.0.16.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0.16 CHECK constraints were parsed but silently ignored before 8.0.16. From 8.0.16 they are enforced. Subqueries are not allowed inside CHECK expressions. Functions must be deterministic.
PostgreSQL ✓ Supported all Full CHECK support. Expressions can call user-defined functions, but they must be immutable for use in constraints. NOT VALID option lets you add a constraint without scanning existing rows (validate later with VALIDATE CONSTRAINT).
SQL Server ✓ Supported all Full CHECK support. WITH NOCHECK option adds the constraint without validating existing rows. Subqueries not allowed. User-defined functions can be called if they are deterministic.
Oracle ✓ Supported all Full CHECK support. SYSDATE, SYSTIMESTAMP, USER, ROWNUM, and sequence references are not allowed in CHECK expressions. ENABLE NOVALIDATE lets you add a constraint without checking existing rows.
SQLite ✓ Supported all CHECK constraints are supported. Unlike other engines, SQLite evaluates them but does not enforce NOT NULL as a separate concept from CHECK. Subqueries are allowed in CHECK expressions (unlike most other engines). Constraints cannot be dropped after creation (the table must be recreated).

Details

MySQL silently ignored CHECK constraints before 8.0.16 — a major source of data integrity bugs for anyone who assumed they were enforced. PostgreSQL's NOT VALID / VALIDATE CONSTRAINT split is uniquely useful for adding constraints to large tables without a blocking table scan. SQLite allows subqueries in CHECK expressions, which no other engine supports.

Standard Syntax

CREATE TABLE employees ( id INT PRIMARY KEY, salary NUMERIC, status VARCHAR(20), CONSTRAINT chk_salary CHECK (salary > 0), CONSTRAINT chk_status CHECK (status IN ('active','inactive','pending')) );

Version Support

MySQL: Since 8.0.16 PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

CHECK constraints were parsed but silently ignored before 8.0.16. From 8.0.16 they are enforced. Subqueries are not allowed inside CHECK expressions. Functions must be deterministic.

CREATE TABLE employees ( id INT PRIMARY KEY, salary DECIMAL(10,2), status VARCHAR(20), CONSTRAINT chk_salary CHECK (salary > 0), CONSTRAINT chk_status CHECK (status IN ('active', 'inactive', 'pending')) ); -- Add to existing table: ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18);

PostgreSQL

Full CHECK support. Expressions can call user-defined functions, but they must be immutable for use in constraints. NOT VALID option lets you add a constraint without scanning existing rows (validate later with VALIDATE CONSTRAINT).

CREATE TABLE employees ( id SERIAL PRIMARY KEY, salary NUMERIC CHECK (salary > 0), status TEXT CHECK (status IN ('active', 'inactive', 'pending')) ); -- Add without scanning existing rows: ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0) NOT VALID; ALTER TABLE employees VALIDATE CONSTRAINT chk_salary;

SQL Server

Full CHECK support. WITH NOCHECK option adds the constraint without validating existing rows. Subqueries not allowed. User-defined functions can be called if they are deterministic.

CREATE TABLE employees ( id INT IDENTITY PRIMARY KEY, salary DECIMAL(10,2) CHECK (salary > 0), status NVARCHAR(20) CHECK (status IN ('active','inactive','pending')) ); -- Add without checking existing rows: ALTER TABLE employees WITH NOCHECK ADD CONSTRAINT chk_salary CHECK (salary > 0);

Oracle

Full CHECK support. SYSDATE, SYSTIMESTAMP, USER, ROWNUM, and sequence references are not allowed in CHECK expressions. ENABLE NOVALIDATE lets you add a constraint without checking existing rows.

CREATE TABLE employees ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, salary NUMBER CONSTRAINT chk_salary CHECK (salary > 0), status VARCHAR2(20) CONSTRAINT chk_status CHECK (status IN ('active','inactive','pending')) ); ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18) ENABLE NOVALIDATE;

SQLite

CHECK constraints are supported. Unlike other engines, SQLite evaluates them but does not enforce NOT NULL as a separate concept from CHECK. Subqueries are allowed in CHECK expressions (unlike most other engines). Constraints cannot be dropped after creation (the table must be recreated).

CREATE TABLE employees ( id INTEGER PRIMARY KEY, salary REAL CHECK (salary > 0), status TEXT CHECK (status IN ('active', 'inactive', 'pending')) ); -- SQLite allows subqueries in CHECK (unusual): CREATE TABLE items ( cat_id INT CHECK (cat_id IN (SELECT id FROM categories)) );