Check Constraints in SQL
Overview
A CHECK constraint is a database-level integrity rule that limits the range or format of values that can be inserted or updated in one or more columns. It allows database administrators and backend engineers to guarantee data validity directly at the storage layer, ensuring that bad data can never enter the database—even if an application service bypasses validation logic or writes direct SQL scripts.
What You Will Learn
In this lesson, you will master:
- Syntax & Implementation: Defining single-column and multi-column comparison CHECK constraints.
- Boolean Evaluation: How
TRUE,FALSE, andUNKNOWN(NULL) impact check constraint evaluation. - Pattern & Range Validation: Enforcing numeric ranges, date boundaries, and string regex patterns.
- Multi-Column Rules: Cross-column validation (e.g., ensuring
end_date >= start_date). - Enabling, Disabling & Performance: Adding check constraints safely to large existing production tables with
NOT VALID.
Core Syntax & Constraint Examples
1. Numeric Range & Status Enum Validation
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(150) NOT NULL,
price NUMERIC(10, 2) NOT NULL,
discount_percent NUMERIC(5, 2) DEFAULT 0.0,
status VARCHAR(20) DEFAULT 'draft',
-- Single-column check: price must be positive
CONSTRAINT chk_products_positive_price CHECK (price > 0),
-- Range check: discount must be between 0% and 100%
CONSTRAINT chk_products_valid_discount CHECK (discount_percent >= 0 AND discount_percent <= 100),
-- Value list check: status must match permitted values
CONSTRAINT chk_products_valid_status CHECK (status IN ('draft', 'published', 'archived'))
);
Multi-Column Cross-Validation
CHECK constraints can reference multiple columns within the same row. This is particularly valuable for date ranges, discount calculations, and conditional requirements:
CREATE TABLE promotional_campaigns (
campaign_id SERIAL PRIMARY KEY,
title VARCHAR(100) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
min_purchase_amount NUMERIC(10, 2),
max_discount_cap NUMERIC(10, 2),
-- Ensure chronological order: end_date must be on or after start_date
CONSTRAINT chk_campaign_date_order CHECK (end_date >= start_date),
-- Cross-column logic: discount cap cannot exceed minimum purchase requirement
CONSTRAINT chk_discount_cap_safe CHECK (
max_discount_cap IS NULL OR max_discount_cap <= min_purchase_amount
)
);
The NULL Value Trap in CHECK Constraints
One of the most critical nuances in SQL check constraints is how NULL values are evaluated:
- A check constraint passes if the conditional expression evaluates to
TRUEORUNKNOWN. - It only fails (and rejects the row) if the expression evaluates strictly to
FALSE.
-- Suppose we have this constraint:
-- CHECK (age >= 18)
-- 1. Row with age = 25: (25 >= 18) -> TRUE (ACCEPTED)
-- 2. Row with age = 15: (15 >= 18) -> FALSE (REJECTED by CHECK constraint)
-- 3. Row with age = NULL: (NULL >= 18) -> UNKNOWN (ACCEPTED!)
Important Nuance
If you want to disallow NULL values, you must explicitly pair the check constraint with NOT NULL on the column, or write: CHECK (age IS NOT NULL AND age >= 18).
String Pattern & Format Matching
In PostgreSQL and Oracle, you can use regular expressions inside CHECK constraints:
-- PostgreSQL regex check for RFC-compliant email structure
CREATE TABLE user_profiles (
user_id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL,
zip_code VARCHAR(10),
-- Simple email format check
CONSTRAINT chk_valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
-- US 5-digit zip code check
CONSTRAINT chk_valid_zip CHECK (zip_code ~ '^\d{5}$')
);
Adding Constraints Safely to Massive Existing Tables
In high-traffic production databases, executing ALTER TABLE ... ADD CONSTRAINT CHECK (...) requires a full table scan and takes an exclusive table lock, which can cause connection timeouts.
In PostgreSQL, you can add constraints in two non-blocking steps:
-- Step 1: Add constraint for new writes only (Instantaneous, no table lock)
ALTER TABLE orders
ADD CONSTRAINT chk_orders_positive_total
CHECK (total_amount >= 0) NOT VALID;
-- Step 2: Validate existing rows in the background without blocking concurrent writes
ALTER TABLE orders
VALIDATE CONSTRAINT chk_orders_positive_total;
Common Mistakes & Gotchas
- Subqueries are Forbidden: A CHECK constraint can only evaluate columns within the current row. You cannot reference other tables or run subqueries (
CHECK (user_id IN (SELECT id FROM users))is invalid; use aFOREIGN KEYinstead). - Non-Deterministic Functions are Forbidden: You cannot call non-deterministic functions like
CURRENT_DATE,RANDOM(), orNOW()in a CHECK constraint because row validity would change over time. - MySQL Historical Quirk: Prior to MySQL 8.0.16, MySQL parsed CHECK constraints syntactically but silently ignored them without enforcing any validation. Always use MySQL 8.0.16+ if relying on CHECK constraints.
Interview Perspective
SQL Interview Question: How does a CHECK constraint evaluate NULL values, and why can't you use subqueries inside it?
Key Talking Points for Candidates:
- Three-Valued Logic: Check constraints reject a row only when the check condition evaluates to
FALSE. An expression comparing againstNULLevaluates toUNKNOWN, which permits the row to be inserted. - Row-Local Invariant: By ANSI SQL specification, CHECK constraints enforce invariants that are verifiable entirely within the tuple itself without locks on other tables.
- Referential Integrity: Cross-table dependencies must be enforced via
FOREIGN KEYconstraints, triggers, or materialized views.
Interactive Practice Challenge
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.