SQL guide · Constraints

SQL CHECK Constraint: Enforce Rules on Column Values

A CHECK constraint attaches a condition to a column or a table, and every inserted or updated row must satisfy it. If the condition evaluates to false, the statement fails. This lets you encode business rules directly in the schema: prices cannot be negative, a discount must be between 0 and 100, a status must be one of a short list, or an end date must not be before a start date.

Because the rule lives in the database, it applies no matter which application, script or analyst writes the data. That is a major advantage over validation in application code alone, which is easy to bypass or forget. The condition can use comparison operators, AND, OR, IN, BETWEEN, LIKE and most scalar functions, and a table-level check can compare several columns in the same row.

One detail matters a lot: a check passes when the condition evaluates to unknown, which is what happens when a compared column is NULL. For example CHECK (price > 0) still allows a null price. If the column must also be present, combine the check with NOT NULL. Checks cannot normally reference other tables or run subqueries, so use a foreign key or trigger for cross-table rules.

Basic syntax

CREATE TABLE table_name (
  column1 data_type CHECK (condition),
  column2 data_type,
  CONSTRAINT chk_name CHECK (condition_using_columns)
);

ALTER TABLE table_name
ADD CONSTRAINT chk_name CHECK (condition);

condition is any boolean expression over columns of the same row. A constraint written beside a column normally refers to that column, while a named table-level constraint can refer to several. Naming the constraint gives you a clear error message and an easy way to drop it later.

SQL CHECK examples

Restrict a numeric range

Products must have a positive price and a stock quantity of zero or more. Inserting a negative price produces a constraint violation naming the rule.

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name       VARCHAR(120) NOT NULL,
  price      NUMERIC(10,2) NOT NULL CHECK (price > 0),
  stock_qty  INT NOT NULL,
  CONSTRAINT chk_stock_nonnegative CHECK (stock_qty >= 0)
);

Limit a column to allowed values

An IN list works like an enumeration. Only the listed statuses can be stored, which prevents typos such as shiped from creeping into reports.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  status   VARCHAR(20) NOT NULL DEFAULT 'pending',
  CONSTRAINT chk_order_status
    CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);

Compare two columns in the same row

A table-level check can enforce relationships between columns. This one makes sure a promotion cannot end before it starts. It is checked on every insert and update.

CREATE TABLE promotions (
  promo_id   INT PRIMARY KEY,
  start_date DATE NOT NULL,
  end_date   DATE NOT NULL,
  CONSTRAINT chk_promo_dates CHECK (end_date >= start_date)
);

Add a check to an existing table

Existing rows are validated when you add the constraint, so a single bad row blocks the change. Find the offenders first, fix them, then add the rule.

SELECT * FROM employees WHERE salary < 0;

ALTER TABLE employees
ADD CONSTRAINT chk_salary_positive CHECK (salary >= 0);

Where SQL dialects differ

PostgreSQL: Fully supported, and checks may use immutable functions. ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT avoids a long lock on big tables. Drop with ALTER TABLE t DROP CONSTRAINT chk_name.

MySQL: MySQL parsed but silently ignored CHECK constraints before 8.0.16. From 8.0.16 they are enforced. Use ALTER TABLE t DROP CHECK chk_name (or DROP CONSTRAINT in newer 8.0 releases). Checks cannot use stored functions or subqueries.

SQL Server: Supported with ADD CONSTRAINT. Constraints added with WITH NOCHECK skip existing rows and are marked not trusted, which can prevent the optimizer from using them. User-defined functions are allowed in checks but can hurt performance.

SQLite: Enforced when declared in CREATE TABLE. SQLite cannot add a check to an existing table with ALTER TABLE, so you must rebuild the table. Checks are skipped when you run the pragma ignore_check_constraints.

Oracle: Supports CHECK inline or through ALTER TABLE ... ADD CONSTRAINT. Conditions cannot use subqueries or non-deterministic functions such as SYSDATE. Constraints can be created with ENABLE NOVALIDATE to skip existing rows.

Common mistakes

Expecting CHECK (col > 0) to reject nulls. A null makes the condition unknown, and unknown passes, so also declare the column NOT NULL.
Writing a rule that references another table. Checks only see the current row; use a foreign key for lookup lists or a trigger for complex rules.
Running on an old MySQL version and assuming the constraint works. Before 8.0.16 it had no effect at all.
Leaving constraints unnamed. Auto-generated names make error messages cryptic and make DROP CONSTRAINT scripts hard to write.
Hard-coding values that change often, such as a list of countries. Put those in a lookup table with a foreign key instead.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator