MySQL CHECK Constraint

A CHECK constraint defines a condition that data must satisfy.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    age INT CHECK (age >= 18)
);

Here, the age must be at least 18.

CHECK constraints help maintain valid data in a database.

• Prevent invalid ages
• Prevent negative fees
• Restrict status values
• Validate quantities
• Validate marks or percentages
• Apply business rules at the database level

The basic syntax is:

column_name data_type CHECK (condition)

Example:

age INT CHECK (age >= 18)

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT CHECK (age >= 18)
);

The age value must satisfy the condition age >= 18.

INSERT INTO students
(student_id, name, age)
VALUES
(1, 'Amit', 21);

This record is valid because 21 satisfies the condition age >= 18.

INSERT INTO students
(student_id, name, age)
VALUES
(2, 'Rahul', 15);

The insert is rejected because 15 does not satisfy age >= 18.

You can use the greater-than operator in a CHECK condition.

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

The price must be greater than zero.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    marks INT CHECK (marks >= 0)
);

Marks cannot be less than zero.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    age INT CHECK (age < 100)
);

The age must be less than 100.

CHECK can use BETWEEN to define a range.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    percentage DECIMAL(5,2)
    CHECK (percentage BETWEEN 0 AND 100)
);

The percentage must be between 0 and 100.

CHECK can restrict a column to a list of allowed values.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    status VARCHAR(20)
    CHECK (status IN ('Active', 'Inactive'))
);

Only Active or Inactive values satisfy this condition.

CHECK conditions can use comparison operators such as:

• >
• <
• >=
• <=
• =
• <>

age INT CHECK (age >= 18)

You can give a CHECK constraint a name.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    age INT,

    CONSTRAINT chk_student_age
    CHECK (age >= 18)
);

The constraint is named chk_student_age.

Naming constraints makes database definitions easier to understand and manage.

CONSTRAINT chk_student_age
CHECK (age >= 18)

The name helps identify which rule is being enforced.

A table can contain multiple CHECK constraints.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    age INT CHECK (age >= 18),
    percentage DECIMAL(5,2)
    CHECK (percentage BETWEEN 0 AND 100)
);

Both conditions must be satisfied.

Multiple conditions can be combined using AND.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    age INT,
    percentage DECIMAL(5,2),

    CHECK (age >= 18 AND percentage >= 0)
);

Both conditions must be satisfied for the row to pass the CHECK condition.

OR can be used when one of multiple conditions should be satisfied.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    status VARCHAR(20),

    CHECK (
        status = 'Active'
        OR status = 'Inactive'
    )
);

NOT can be used inside a CHECK condition.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    status VARCHAR(20),

    CHECK (status NOT IN ('Deleted'))
);

This prevents the value Deleted from being accepted.

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    price DECIMAL(10,2)
    CHECK (price > 0)
);

A product price must be greater than zero.

CREATE TABLE results (
    result_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    marks INT CHECK (marks BETWEEN 0 AND 100)
);

This prevents marks outside the 0–100 range.

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    quantity INT CHECK (quantity > 0)
);

The quantity must be greater than zero.

CHECK and NOT NULL can be used together.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    age INT NOT NULL CHECK (age >= 18)
);

Here, age must be provided and must be at least 18.

CHECK can be combined with DEFAULT.

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    quantity INT NOT NULL DEFAULT 1
    CHECK (quantity > 0)
);

If quantity is omitted, MySQL uses 1, which satisfies the CHECK condition.

You can add a CHECK constraint to an existing table.

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

Existing rows must satisfy the condition for the constraint to be successfully added.

A named CHECK constraint can be removed using ALTER TABLE.

ALTER TABLE students
DROP CHECK chk_age;

The constraint name must match the actual CHECK constraint name.

• Writing an incorrect condition
• Using values that violate the condition
• Forgetting to consider existing data when adding a CHECK
• Using CHECK when application logic is actually required
• Forgetting the constraint name when trying to remove it
• Assuming older MySQL versions enforce CHECK constraints in the same way as current versions
• Creating a condition that conflicts with DEFAULT values

You can inspect the table definition using:

SHOW CREATE TABLE students;

This can show the CHECK constraint definitions.

You can also use MySQL's information_schema metadata to inspect table constraints.

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    percentage DECIMAL(5,2),

    CONSTRAINT chk_student_age
    CHECK (age >= 18),

    CONSTRAINT chk_student_percentage
    CHECK (percentage BETWEEN 0 AND 100)
);

This table ensures that age is at least 18 and percentage remains between 0 and 100.

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    fee DECIMAL(10,2) NOT NULL,
    duration_months INT NOT NULL,

    CONSTRAINT chk_course_fee
    CHECK (fee >= 0),

    CONSTRAINT chk_course_duration
    CHECK (duration_months > 0)
);

INSERT INTO courses
(course_name, fee, duration_months)
VALUES
('Python Full Stack', 15000.00, 12);

The fee cannot be negative and the course duration must be greater than zero.

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    percentage DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(20) NOT NULL DEFAULT 'Active',

    CONSTRAINT chk_student_age
    CHECK (age >= 18),

    CONSTRAINT chk_student_percentage
    CHECK (percentage BETWEEN 0 AND 100),

    CONSTRAINT chk_student_status
    CHECK (status IN ('Active', 'Inactive'))
);

INSERT INTO students
(student_code, name, age, percentage)
VALUES
('STU001', 'Amit Kumar', 21, 85.50);

SELECT *
FROM students;

SHOW CREATE TABLE students;

This table uses UNIQUE, NOT NULL, DEFAULT, and CHECK constraints together to maintain valid student data.