# SQL CHECK Constraint: Enforce Rules on Column Values

- URL: https://betteranalyst.com/sql/check

SQL guide · Constraints

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

- [SQL NOT NULL](https://betteranalyst.com/sql/not-null)
- [SQL DEFAULT](https://betteranalyst.com/sql/default)
- [SQL ADD CONSTRAINT](https://betteranalyst.com/sql/add-constraint)
- [SQL DROP CONSTRAINT](https://betteranalyst.com/sql/drop-constraint)
- [SQL IN](https://betteranalyst.com/sql/in)
- [SQL BETWEEN](https://betteranalyst.com/sql/between)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)