SQL Check Constraint

The CHECK constraint is used to limit the value range that can be placed in a column. It enforces domain integrity by evaluating a boolean expression for each row before allowing an INSERT or UPDATE operation to succeed.

If the evaluated expression returns TRUE (or NULL in most contexts), the data modification is accepted. If the condition evaluates to FALSE, the transaction fails with a constraint violation error.

Basic Syntax & Examples

CHECK constraints can be defined at the column level (referencing a single column) or at the table level (referencing multiple columns).

1. Column-Level Check Constraint

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName  VARCHAR(50) NOT NULL,
    Salary     DECIMAL(10, 2) CHECK (Salary >= 1000.00),
    Age        INT CHECK (Age >= 18 AND Age <= 65)
);

In this table:

• Salary must be at least 1000.00.
• Age must be between 18 and 65 inclusive.

2. Table-Level Check Constraint (Multi-Column Comparison)

When a validation rule depends on comparing values across multiple columns in the same row, you must declare a table-level CHECK constraint:

CREATE TABLE Products (
    ProductID    INT PRIMARY KEY,
    ProductName  VARCHAR(100) NOT NULL,
    StandardCost DECIMAL(10, 2) NOT NULL,
    ListPrice    DECIMAL(10, 2) NOT NULL,
    
    -- Table-level constraint ensuring price is greater than cost
    CONSTRAINT CHK_Price_GreaterThan_Cost CHECK (ListPrice > StandardCost)
);

3. Using Functions & Set Operators in CHECK Constraints

CHECK conditions can leverage logical operators (AND, OR, NOT), set operations (IN), pattern matching (LIKE), or standard scalar functions.

CREATE TABLE Orders (
    OrderID      INT PRIMARY KEY,
    Status       VARCHAR(20) CHECK (Status IN ('Pending', 'Shipped', 'Delivered', 'Cancelled')),
    PromoCode    VARCHAR(10) CHECK (PromoCode LIKE 'PROMO-%'),
    OrderDate    DATE CHECK (OrderDate <= CURRENT_DATE)
);

• Status: Restricts allowed values strictly to four specific status strings.
• PromoCode: Enforces that any entered promotional code starts with the prefix 'PROMO-'.

Adding a CHECK Constraint to an Existing Table

You can add constraints to existing tables using ALTER TABLE:

ALTER TABLE Employees
ADD CONSTRAINT CHK_Employee_Email 
CHECK (Email LIKE '%@%.%');

Handling Existing Data (WITH NOCHECK in SQL Server): By default, adding a CHECK constraint validates all existing rows in the table. If existing rows violate the rule, the ALTER TABLE statement fails.

Dropping a CHECK Constraint

To remove an active CHECK constraint by name:

-- PostgreSQL / SQL Server / Oracle
ALTER TABLE Employees
DROP CONSTRAINT CHK_Employee_Email;

-- MySQL / MariaDB (MySQL 8.0.16+)
ALTER TABLE Employees
DROP CHECK CHK_Employee_Email;

Behavior with NULL Values

An important nuance of SQL CHECK constraints is how they evaluate NULL entries:

• In SQL predicate logic, if a column value is NULL, most comparison conditions evaluate to UNKNOWN rather than FALSE.
• Because CHECK constraints only block operations where the expression evaluates explicitly to FALSE, a NULL value will pass the check unless the column is also defined as NOT NULL.

CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY,
    -- NULL values ARE allowed here unless NOT NULL is added explicitly
    Balance   DECIMAL(10, 2) CHECK (Balance >= 0.00) 
);

Engine Support & Compatibility

• PostgreSQL / SQL Server / Oracle: Full, robust support for CHECK constraints, including named constraints and multi-column rules.
• MySQL: Prior to version 8.0.16, MySQL parsed CHECK constraint syntax but silently ignored it without enforcing the rules. MySQL 8.0.16 and later fully enforce CHECK constraints.
• SQLite: Supported in modern versions, evaluated during inserts and updates.