SQL - CHECK Constraint | Application Architect

Key Insights

CHECK constraints enforce domain integrity by validating data against boolean expressions before INSERT or UPDATE operations, catching invalid data at the database layer rather than application code
Modern databases support both column-level and table-level CHECK constraints, with table-level constraints enabling complex multi-column validation logic
Performance impact is minimal for simple checks, but complex expressions with subqueries or functions can create bottlenecks requiring careful index planning and constraint design

Understanding CHECK Constraints

CHECK constraints define business rules directly in the database schema by specifying conditions that column values must satisfy. Unlike foreign key constraints that reference other tables, CHECK constraints evaluate boolean expressions against the row being modified. If the expression returns FALSE, the database rejects the operation.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    discount_percent INT CHECK (discount_percent BETWEEN 0 AND 100),
    stock_quantity INT CHECK (stock_quantity >= 0)
);

This example prevents negative prices, invalid discount percentages, and negative inventory counts. The constraint fires automatically on every INSERT and UPDATE statement.

Column-Level vs Table-Level Constraints

Column-level CHECK constraints appear immediately after the column definition and can only reference that specific column. Table-level constraints are defined separately and can validate relationships between multiple columns.

-- Column-level: Simple, single-column validation
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    salary DECIMAL(10,2) CHECK (salary >= 15000),
    hire_date DATE CHECK (hire_date <= CURRENT_DATE)
);

-- Table-level: Multi-column validation
CREATE TABLE project_assignments (
    assignment_id INT PRIMARY KEY,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    estimated_hours INT,
    actual_hours INT,
    CONSTRAINT valid_date_range CHECK (end_date >= start_date),
    CONSTRAINT valid_hours CHECK (
        actual_hours IS NULL OR actual_hours >= 0
    ),
    CONSTRAINT realistic_estimate CHECK (
        estimated_hours IS NULL OR 
        estimated_hours BETWEEN 1 AND 2000
    )
);

Named constraints using CONSTRAINT keyword make error messages clearer and enable easy dropping or modification later.

Complex Validation Logic

CHECK constraints support complex boolean expressions including CASE statements, pattern matching, and multiple conditions combined with AND/OR operators.

CREATE TABLE customer_orders (
    order_id INT PRIMARY KEY,
    customer_type VARCHAR(20),
    order_amount DECIMAL(10,2),
    payment_method VARCHAR(20),
    credit_limit DECIMAL(10,2),
    CONSTRAINT valid_customer_type CHECK (
        customer_type IN ('RETAIL', 'WHOLESALE', 'ENTERPRISE')
    ),
    CONSTRAINT payment_validation CHECK (
        CASE 
            WHEN customer_type = 'RETAIL' 
                THEN payment_method IN ('CREDIT_CARD', 'DEBIT_CARD', 'CASH')
            WHEN customer_type = 'WHOLESALE'
                THEN payment_method IN ('CREDIT_CARD', 'NET30', 'NET60')
            WHEN customer_type = 'ENTERPRISE'
                THEN payment_method IN ('NET30', 'NET60', 'NET90')
        END
    ),
    CONSTRAINT credit_limit_check CHECK (
        (payment_method LIKE 'NET%' AND order_amount <= credit_limit)
        OR payment_method NOT LIKE 'NET%'
    )
);

This design enforces business rules: retail customers cannot use NET payment terms, wholesale customers have limited payment options, and NET payment orders cannot exceed credit limits.

Pattern Matching and String Validation

CHECK constraints validate string formats using LIKE patterns or regular expressions (database-dependent).

CREATE TABLE user_accounts (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    phone_number VARCHAR(20),
    postal_code VARCHAR(10),
    CONSTRAINT valid_username CHECK (
        username ~ '^[a-zA-Z0-9_]{3,50}$'  -- PostgreSQL regex
    ),
    CONSTRAINT valid_email CHECK (
        email LIKE '%_@__%.__%'
    ),
    CONSTRAINT valid_phone CHECK (
        phone_number ~ '^\+?[1-9]\d{1,14}$'
    ),
    CONSTRAINT valid_postal CHECK (
        postal_code ~ '^\d{5}(-\d{4})?$'  -- US ZIP code format
    )
);

-- MySQL equivalent using REGEXP
CREATE TABLE user_accounts_mysql (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    CONSTRAINT valid_username CHECK (
        username REGEXP '^[a-zA-Z0-9_]{3,50}$'
    ),
    CONSTRAINT valid_email CHECK (
        email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'
    )
);

Note that regular expression syntax varies between database systems. PostgreSQL uses ~, MySQL uses REGEXP, and SQL Server uses LIKE with wildcards.

Handling NULL Values

CHECK constraints treat NULL values specially—they pass the constraint unless explicitly checked. This follows SQL’s three-valued logic (TRUE, FALSE, UNKNOWN).

CREATE TABLE product_inventory (
    product_id INT PRIMARY KEY,
    reorder_point INT,
    reorder_quantity INT,
    max_stock_level INT,
    -- This allows NULL but validates non-NULL values
    CONSTRAINT positive_reorder CHECK (
        reorder_point IS NULL OR reorder_point > 0
    ),
    -- This requires a value and validates it
    CONSTRAINT required_max_stock CHECK (
        max_stock_level IS NOT NULL AND max_stock_level > 0
    ),
    -- Complex NULL handling
    CONSTRAINT reorder_logic CHECK (
        (reorder_point IS NULL AND reorder_quantity IS NULL) OR
        (reorder_point IS NOT NULL AND reorder_quantity IS NOT NULL 
         AND reorder_quantity > 0)
    )
);

The last constraint ensures that reorder_point and reorder_quantity are either both NULL or both have valid values—preventing incomplete reorder configurations.

Performance Considerations

CHECK constraints execute on every INSERT and UPDATE. Simple comparisons have negligible overhead, but complex expressions impact performance.

-- Fast: Direct column comparison
CREATE TABLE orders_fast (
    order_id INT PRIMARY KEY,
    quantity INT CHECK (quantity > 0),
    unit_price DECIMAL(10,2) CHECK (unit_price > 0)
);

-- Slower: Function calls in CHECK
CREATE TABLE orders_slow (
    order_id INT PRIMARY KEY,
    order_date DATE,
    ship_date DATE,
    CONSTRAINT date_validation CHECK (
        EXTRACT(YEAR FROM order_date) >= 2020 AND
        EXTRACT(DOW FROM ship_date) NOT IN (0, 6)  -- No weekend shipping
    )
);

-- Avoid: Subqueries in CHECK (not supported in most databases)
-- CREATE TABLE order_items (
--     item_id INT PRIMARY KEY,
--     product_id INT,
--     quantity INT,
--     CONSTRAINT stock_check CHECK (
--         quantity <= (SELECT stock FROM products WHERE id = product_id)
--     )
-- );

Most databases prohibit subqueries in CHECK constraints. Use triggers for validation requiring data from other tables.

Managing Existing Constraints

Add, drop, or temporarily disable CHECK constraints on existing tables using ALTER TABLE statements.

-- Add constraint to existing table
ALTER TABLE products
ADD CONSTRAINT reasonable_price CHECK (price BETWEEN 0.01 AND 999999.99);

-- Drop constraint
ALTER TABLE products
DROP CONSTRAINT reasonable_price;

-- Disable constraint temporarily (SQL Server)
ALTER TABLE products
NOCHECK CONSTRAINT reasonable_price;

-- Re-enable constraint (SQL Server)
ALTER TABLE products
CHECK CONSTRAINT reasonable_price;

-- PostgreSQL: Disable and re-enable
ALTER TABLE products
VALIDATE CONSTRAINT reasonable_price;

When adding constraints to tables with existing data, the database validates all rows. Use NOT VALID (PostgreSQL) or WITH NOCHECK (SQL Server) to skip existing data validation, then validate later during maintenance windows.

Real-World Example: E-Commerce Order System

CREATE TABLE ecommerce_orders (
    order_id INT PRIMARY KEY,
    order_status VARCHAR(20) NOT NULL,
    order_date TIMESTAMP NOT NULL,
    ship_date TIMESTAMP,
    delivery_date TIMESTAMP,
    subtotal DECIMAL(10,2) NOT NULL,
    tax_amount DECIMAL(10,2) NOT NULL,
    shipping_cost DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2),
    total_amount DECIMAL(10,2) NOT NULL,
    
    CONSTRAINT valid_status CHECK (
        order_status IN ('PENDING', 'CONFIRMED', 'SHIPPED', 
                        'DELIVERED', 'CANCELLED', 'RETURNED')
    ),
    CONSTRAINT valid_dates CHECK (
        order_date <= COALESCE(ship_date, CURRENT_TIMESTAMP) AND
        ship_date <= COALESCE(delivery_date, CURRENT_TIMESTAMP)
    ),
    CONSTRAINT positive_amounts CHECK (
        subtotal >= 0 AND
        tax_amount >= 0 AND
        shipping_cost >= 0 AND
        COALESCE(discount_amount, 0) >= 0
    ),
    CONSTRAINT total_calculation CHECK (
        total_amount = subtotal + tax_amount + shipping_cost - 
                      COALESCE(discount_amount, 0)
    ),
    CONSTRAINT logical_status_dates CHECK (
        (order_status IN ('PENDING', 'CONFIRMED') AND ship_date IS NULL) OR
        (order_status = 'SHIPPED' AND ship_date IS NOT NULL) OR
        (order_status = 'DELIVERED' AND delivery_date IS NOT NULL) OR
        (order_status IN ('CANCELLED', 'RETURNED'))
    )
);

This design prevents invalid order states, ensures date sequences make sense, validates financial calculations, and enforces business rules about status transitions—all at the database level where they cannot be bypassed.