SQL CHECK constraint

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

2. Salary must be positive

3. Status must be one of defined values

4. End date must be after start date

5. Quantity cannot exceed inventory

 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)

This ensures:

• Discount cannot exceed price

• Discount cannot be negative

 CHECK vs Other Constraints

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