CodeCapsule

SQL CHECK constraint

by

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 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)

CREATE TABLE employees (
id INT PRIMARY KEY,
age INT CHECK (age >= 18),
salary DECIMAL(10,2) CHECK (salary > 0)
);

🔹 Named CHECK Constraint (Best Practice)

CREATE TABLE products (
id INT PRIMARY KEY,
price DECIMAL(10,2),
CONSTRAINT chk_price_positive CHECK (price > 0)
);

✔ Naming constraints makes maintenance easier.

🔥 Add CHECK Constraint to Existing Table

PostgreSQL / SQL Server:

ALTER TABLE employees
ADD CONSTRAINT chk_age CHECK (age >= 18);

MySQL:

ALTER TABLE employees
ADD CONSTRAINT chk_age CHECK (age >= 18);

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

🔥 Remove CHECK Constraint

PostgreSQL / SQL Server:

ALTER TABLE employees
DROP CONSTRAINT chk_age;

MySQL:

ALTER TABLE employees
DROP CHECK chk_age;

📌 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)

CREATE TABLE orders (
id INT PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL,
order_date DATE NOT NULL,
CONSTRAINT chk_amount CHECK (amount > 0),
CONSTRAINT chk_status CHECK (status IN ('pending', 'paid', 'shipped'))
);

🧠 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

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *