SQL CHECK constraint

SQL Tutorial

Here is a clear, practical, and complete guide to the SQL CHECK constraint, including syntax, examples, engine differences, common pitfalls, and best-practice usage.

✅ What the SQL CHECK constraint Does

A CHECK constraint enforces a condition that must be true for each row in a table.

It prevents invalid data from being inserted or updated — for example:

  • Age must be positive

  • Salary must be greater than 0

  • Status must be one of a defined set

  • Dates must be in the future or past

🔹 Basic Syntax (CREATE TABLE)


🔹 Named CHECK Constraint (Best Practice)


✔ Naming constraints makes maintenance easier.

🔥 Add CHECK Constraint to Existing Table

PostgreSQL / SQL Server:


MySQL:


(MySQL properly supports CHECK starting from version 8.0.16; older versions ignore CHECK constraints.)

🔥 Remove CHECK Constraint

PostgreSQL / SQL Server:


MySQL:


📌 Common Real-World Examples

1. Valid age range

CHECK (age BETWEEN 18 AND 65)

2. Salary must be positive

CHECK (salary > 0)

3. Status must be one of defined values

CHECK (status IN ('active', 'inactive', 'pending'))

4. End date must be after start date

CHECK (end_date > start_date)

5. Quantity cannot exceed inventory

CHECK (quantity <= stock_available)

🔧 CHECK Constraint in CREATE TABLE (Complete Example)


🧠 Important Behavior Notes

✔ CHECK constraints evaluate to:

  • TRUE → allowed

  • FALSE → rejected

  • NULL → allowed (unless column is NOT NULL)

✔ CHECK constraints do not run when selecting rows

They run only on INSERT and UPDATE.

✔ MySQL 5.x ignored CHECK — ensure using 8.0.16+

✔ Multiple CHECK constraints can be added to the same table.

🔥 Composite CHECK Constraints (Multiple Columns)

CHECK (discount <= price AND discount >= 0)

This ensures:

  • Discount cannot exceed price

  • Discount cannot be negative

📌 CHECK vs Other Constraints

Constraint Purpose
CHECK Enforce custom conditions
NOT NULL Prevent NULL values
UNIQUE Prevent duplicates
PRIMARY KEY Unique + NOT NULL
FOREIGN KEY Enforce relationships

CHECK is the most flexible constraint.

🏎 Best Practices

✔ Use named CHECK constraints (e.g., chk_salary_positive)
✔ Validate business rules at database level — not just in app code
✔ Combine CHECK with NOT NULL for mandatory fields
✔ Avoid overly complex expressions that hinder performance
✔ Keep constraints simple, readable, and explainable

You may also like...