Learn SQL Foundation Data Types & Constraints
Lesson 16

CHECK Constraint in SQL: Validating Column Values

Define rules that control exactly what values are allowed in a column so invalid data never reaches your tables.

A product row with a negative price gets inserted. An order lands in the database with a status of 'Refunded' when the only valid values are 'Pending', 'Shipped', and 'Delivered'. A discount percentage of 150 slips through. All of these are values the data type alone cannot block. The CHECK constraint is how you encode your business rules directly into the schema so the database enforces them for you.

What the CHECK Constraint Does

CHECK takes any valid SQL expression that returns a boolean. If the expression evaluates to TRUE or NULL on insert or update, the row is accepted. If it evaluates to FALSE, the database rejects the operation with a constraint error.

-- CHECK constraints on the products table
CREATE TABLE products (
    product_id    INTEGER PRIMARY KEY,
    product_name  TEXT    NOT NULL,
    price         REAL    NOT NULL CHECK (price > 0),
    cost_price    REAL    NOT NULL CHECK (cost_price >= 0),
    stock_qty     INTEGER NOT NULL DEFAULT 0 CHECK (stock_qty >= 0),
    is_active     INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1)),
    created_at    TEXT    NOT NULL
);

Each CHECK here enforces a specific business rule: prices must be positive, stock cannot go negative, and is_active can only be 0 or 1. None of these rules can be enforced by data type alone.

-- This INSERT would fail: price cannot be negative
-- INSERT INTO products (product_id, product_name, price, cost_price, stock_qty, created_at)
-- VALUES (99, 'Test Item', -5.00, 2.00, 10, '2024-01-01');

Inline vs Table-Level CHECK Constraints

You can write a CHECK constraint inline with the column definition or at the table level. Inline works for single-column rules. Table-level is required when the rule spans multiple columns.

-- Inline CHECK: applies to one column
CREATE TABLE employees (
    employee_id  INTEGER PRIMARY KEY,
    first_name   TEXT    NOT NULL,
    last_name    TEXT    NOT NULL,
    email        TEXT    NOT NULL UNIQUE,
    salary       REAL    NOT NULL CHECK (salary > 0),
    hire_date    TEXT    NOT NULL,
    role         TEXT    NOT NULL CHECK (role IN ('Manager', 'Analyst', 'Engineer', 'Support', 'Sales')),
    department   TEXT    NOT NULL
);

-- Table-level CHECK: spans two columns
CREATE TABLE orders (
    order_id      INTEGER PRIMARY KEY,
    customer_id   INTEGER NOT NULL,
    order_date    TEXT    NOT NULL,
    shipped_date  TEXT,
    status        TEXT    NOT NULL CHECK (status IN ('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled')),
    total_amount  REAL    NOT NULL CHECK (total_amount >= 0),
    CHECK (shipped_date IS NULL OR shipped_date >= order_date)
);

The last constraint ensures shipped_date cannot be earlier than order_date. This kind of cross-column logic is only possible at the table level.

Key idea: A CHECK constraint is evaluated per row on every INSERT and UPDATE. It cannot reference other tables, use subqueries, or call non-deterministic functions. Keep your checks simple and focused on the values within the same row.

Common CHECK Patterns for Ecommerce Data

Here are the types of checks you would apply across the ecommerce schema:

-- Range checks on financial columns
CREATE TABLE order_items (
    item_id      INTEGER PRIMARY KEY,
    order_id     INTEGER NOT NULL,
    product_id   INTEGER NOT NULL,
    quantity     INTEGER NOT NULL CHECK (quantity > 0),
    unit_price   REAL    NOT NULL CHECK (unit_price > 0),
    discount_pct REAL             CHECK (discount_pct IS NULL OR (discount_pct >= 0 AND discount_pct <= 1)),
    line_total   REAL    NOT NULL CHECK (line_total >= 0)
);

-- Enumerated value checks on status and method columns
CREATE TABLE payments (
    payment_id  INTEGER PRIMARY KEY,
    order_id    INTEGER NOT NULL,
    amount      REAL    NOT NULL CHECK (amount > 0),
    method      TEXT    NOT NULL CHECK (method IN ('Credit Card', 'Debit Card', 'PayPal', 'Bank Transfer', 'Cash')),
    status      TEXT    NOT NULL CHECK (status IN ('Pending', 'Completed', 'Failed', 'Refunded')),
    payment_date TEXT   NOT NULL
);

Auditing Existing Data for CHECK Violations

When you add a CHECK constraint to a table that already has data, the database validates all existing rows. If any row fails, the constraint cannot be added. Before adding constraints to a live table, audit first.

-- Find products with invalid prices before adding a CHECK
SELECT p.product_id, p.product_name, p.price, p.cost_price, p.stock_qty
FROM products p
WHERE p.price <= 0
   OR p.cost_price < 0
   OR p.stock_qty < 0;

-- Find order_items with out-of-range discount percentages
SELECT oi.item_id, oi.order_id, oi.product_id, oi.discount_pct
FROM order_items oi
WHERE oi.discount_pct IS NOT NULL
  AND (oi.discount_pct < 0 OR oi.discount_pct > 1);

-- Find payments with unexpected status values
SELECT pay.payment_id, pay.order_id, pay.status, pay.method
FROM payments pay
WHERE pay.status NOT IN ('Pending', 'Completed', 'Failed', 'Refunded')
   OR pay.method NOT IN ('Credit Card', 'Debit Card', 'PayPal', 'Bank Transfer', 'Cash');

-- Find orders where shipped_date is earlier than order_date
SELECT o.order_id, o.order_date, o.shipped_date
FROM orders o
WHERE o.shipped_date IS NOT NULL
  AND o.shipped_date < o.order_date;

Naming CHECK Constraints

In MySQL and PostgreSQL you can name constraints to get clearer error messages. SQLite supports named constraints in the syntax but does not surface the name in error messages.

-- Named CHECK constraints (syntax supported in SQLite)
CREATE TABLE products (
    product_id   INTEGER PRIMARY KEY,
    product_name TEXT    NOT NULL,
    price        REAL    NOT NULL,
    stock_qty    INTEGER NOT NULL DEFAULT 0,
    is_active    INTEGER NOT NULL DEFAULT 1,
    CONSTRAINT chk_price_positive    CHECK (price > 0),
    CONSTRAINT chk_stock_non_negative CHECK (stock_qty >= 0),
    CONSTRAINT chk_is_active_flag     CHECK (is_active IN (0, 1))
);

Naming constraints is a good practice even in SQLite. It documents intent and makes schema migrations easier to reason about.

Common Mistakes

-- wrong
CREATE TABLE products (
    product_id  INTEGER PRIMARY KEY,
    price       REAL NOT NULL CHECK (price > 0),
    cost_price  REAL NOT NULL,
    CHECK (cost_price < price)  -- references price which may not yet be validated
);

-- correct
CREATE TABLE products (
    product_id  INTEGER PRIMARY KEY,
    price       REAL NOT NULL CHECK (price > 0),
    cost_price  REAL NOT NULL CHECK (cost_price >= 0),
    CHECK (cost_price <= price)
);

The table-level check compares two column values in the same row, which is valid. What you cannot do is reference another table or use a subquery inside a CHECK expression.

---

-- wrong
SELECT p.product_id, p.price
FROM products p
WHERE p.price != CHECK (price > 0);

-- correct
SELECT p.product_id, p.price
FROM products p
WHERE p.price <= 0;

CHECK is schema syntax, not a query operator. To find rows that would violate a check rule, write the equivalent condition directly in your WHERE clause.

---

-- wrong
CREATE TABLE order_items (
    item_id      INTEGER PRIMARY KEY,
    discount_pct REAL CHECK (discount_pct >= 0 AND discount_pct <= 1)
);
-- Assumes NULL discount_pct is blocked by the CHECK

-- correct
CREATE TABLE order_items (
    item_id      INTEGER PRIMARY KEY,
    discount_pct REAL CHECK (discount_pct IS NULL OR (discount_pct >= 0 AND discount_pct <= 1))
);

A CHECK constraint that does not account for NULL will silently pass NULL values because NULL AND anything evaluates to NULL, not FALSE. If NULL should be allowed, make it explicit in the condition.

Quick Recap

  • CHECK enforces a business rule on every INSERT and UPDATE. If the expression returns FALSE, the operation is rejected. NULL passes through unless you explicitly handle it.
  • Use inline CHECK for single-column rules like range or enumeration checks. Use table-level CHECK when the rule involves comparing two columns in the same row.
  • Before adding a CHECK constraint to an existing table, audit for violations first using WHERE conditions that mirror the check expression. Any failing row will block the constraint from being added.
Exercise 1 Easy
Write a query that finds all rows in the products table that would violate a CHECK constraint of price > 0. Show product_id, product_name, and price. Sort by price ascending.
Hint: Use WHERE p.price <= 0 to mirror the failing condition of the check expression.
Solution
SELECT p.product_id, p.product_name, p.price FROM products p WHERE p.price <= 0 ORDER BY p.price ASC;
Exercise 2 Medium
Write a query that finds all orders where either the total_amount is less than or equal to zero, or the shipped_date is earlier than the order_date. These represent two separate CHECK violations. Show order_id, order_date, shipped_date, status, and total_amount. Sort by order_id ascending.
Hint: Combine two conditions with OR. For the date check, wrap it in shipped_date IS NOT NULL AND shipped_date < order_date to avoid false positives on unshipped orders.
Solution
SELECT o.order_id, o.order_date, o.shipped_date, o.status, o.total_amount FROM orders o WHERE o.total_amount <= 0 OR (o.shipped_date IS NOT NULL AND o.shipped_date < o.order_date) ORDER BY o.order_id ASC;
Exercise 3 Hard
Write a data quality audit query that counts how many rows in order_items violate each of three CHECK rules: quantity must be greater than zero, unit_price must be greater than zero, and discount_pct must be between 0 and 1 when not null. Return a single row with three columns named invalid_quantity, invalid_unit_price, and invalid_discount_pct. Then join to orders to also show how many distinct orders are affected by at least one of these violations, as a fourth column called affected_orders.
Hint: Use COUNT(CASE WHEN ... THEN 1 END) for each violation. For affected_orders, use a subquery or COUNT(DISTINCT oi.order_id) filtered with a CASE or inline condition.
Solution
SELECT COUNT(CASE WHEN oi.quantity <= 0 THEN 1 END) AS invalid_quantity, COUNT(CASE WHEN oi.unit_price <= 0 THEN 1 END) AS invalid_unit_price, COUNT(CASE WHEN oi.discount_pct IS NOT NULL AND (oi.discount_pct < 0 OR oi.discount_pct > 1) THEN 1 END) AS invalid_discount_pct, COUNT(DISTINCT CASE WHEN oi.quantity <= 0 OR oi.unit_price <= 0 OR (oi.discount_pct IS NOT NULL AND (oi.discount_pct < 0 OR oi.discount_pct > 1)) THEN oi.order_id END) AS affected_orders FROM order_items oi;
Frequently Asked Questions

It defines a condition that must evaluate to TRUE for any INSERT or UPDATE to succeed on that row. If the condition fails, the database rejects the operation.

NOT NULL only checks whether a value exists. CHECK lets you validate what the value actually is, such as requiring a price to be greater than zero or a status to be one of a fixed set.

Yes. A table-level CHECK constraint can reference any columns in the same table, allowing rules like shipped_date must be after order_date.

Yes, SQLite enforces CHECK constraints on INSERT and UPDATE. Unlike foreign keys, you do not need to enable them with a PRAGMA statement.