✅ MySQL CHECK Constraint

? Quick Overview

The CHECK constraint in MySQL is used to ensure that values stored in a column satisfy a specific logical condition. It helps you enforce business rules directly at the database level so that invalid data is rejected automatically.

? Key Concepts

Data validation at source: The database itself refuses bad data.
Boolean condition: The expression inside CHECK (...) must be true for every row.
Column-level vs table-level: A CHECK can apply to a single column or multiple columns together.
Examples of rules: minimum age, positive salary, limited status values, valid ranges, etc.

? Syntax

A basic CHECK constraint on a single column can be defined while creating the table.

? View Syntax Example

-- CHECK constraint on a single column
CREATE TABLE Employees (
ID INT,
Name VARCHAR(100),
Age INT CHECK (Age >= 18)
);

You can also combine multiple conditions using logical operators such as AND and OR.

? Full Example: Employees Table

In this example, the Age column must be between 18 and 65, and the Salary must always be a positive value.

? View Table Definition

-- Employees table with multiple CHECK constraints
CREATE TABLE Employees (
ID INT,
Name VARCHAR(100),
Age INT CHECK (Age >= 18 AND Age <= 65),
Salary DECIMAL(10,2) CHECK (Salary > 0)
);

? What Happens When You Insert Data?

When you try to insert a row, MySQL evaluates all CHECK constraints. If every condition is satisfied, the row is inserted; otherwise, the statement fails.

? View INSERT Examples

-- Valid insert: passes all CHECK constraints
INSERT INTO Employees (ID, Name, Age, Salary)
VALUES (1, 'Alice', 25, 50000.00);

-- Invalid insert: fails because Age < 18
INSERT INTO Employees (ID, Name, Age, Salary)
VALUES (2, 'Bob', 16, 40000.00);

? Explanation

For the first INSERT, the values satisfy: Age >= 18 AND Age <= 65 and Salary > 0, so the row is inserted successfully.
For the second INSERT, Age = 16 violates the CHECK (Age >= 18 AND Age <= 65) rule, so MySQL rejects the row and returns an error.

? Tips & Best Practices

Use CHECK to enforce important business rules, such as: minimum age, valid score ranges, non-negative quantities, and positive prices.
Combine conditions for more control, for example: CHECK (Age >= 18 AND Age <= 65) or CHECK (Status IN ('ACTIVE','INACTIVE')).
Keep conditions simple and readable so they are easy to maintain and debug later.
Think carefully before changing or dropping a CHECK constraint, as it can affect existing data rules.

? Try It Yourself

Create a Students table with a CHECK constraint that only allows marks between 0 and 100.
Add a CHECK constraint to a Products table to ensure that Quantity and Price are always positive.
Experiment with combining conditions, such as: CHECK (Age >= 18 AND Country = 'India'), and test different INSERT statements.
Try violating your constraints on purpose to see what error messages MySQL returns.