Check Constraint

Introduction

So far, constraints like PRIMARY KEY, UNIQUE, and NOT NULL enforce structural rules — no duplicates, no missing values. But what if you need to enforce a rule about the actual content of a value, like "age must never be negative" or "price must be greater than zero"?

That is exactly what the CHECK constraint is for. It lets you attach a validation condition directly to a column (or the table), and MySQL refuses to store any row that fails it.

What is a Check Constraint?

CHECK lets you define a boolean condition that every value in a column must satisfy. If an INSERT or UPDATE would produce a value that fails the condition, MySQL rejects the statement entirely and raises an error.

Defining a Check Constraint

You can attach CHECK directly after a column, or define it as a separate, named table constraint — the same two styles you saw with UNIQUE.

-- Inline
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2) CHECK (price > 0)
);

-- Named table constraint
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    CONSTRAINT chk_products_price CHECK (price > 0)
);

Naming the constraint (chk_products_price) makes it easy to identify in error messages and to drop later with ALTER TABLE products DROP CHECK chk_products_price.

Worked Example: Validating Age

Let's enforce that a student's age can never be negative.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT CHECK (age >= 0)
);

INSERT INTO students (id, name, age) VALUES (1, 'Alex', 16);

SELECT * FROM students;

+----+------+-----+
| id | name | age |
+----+------+-----+
| 1  | Alex | 16  |
+----+------+-----+

The insert succeeds because 16 satisfies age >= 0.

What Happens on Violation

Now let's try to insert a student with a negative age.

INSERT INTO students (id, name, age) VALUES (2, 'Sam', -5);

ERROR 3819 (HY000): Check constraint 'students_chk_1' is violated.

MySQL refuses to insert the row at all. No row with age -5 ever makes it into the table, unlike some validation done only in application code, which can be skipped or bypassed.

Multiple Check Constraints

A table can have several CHECK constraints, and a single CHECK can even reference more than one column.

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2) CHECK (price > 0),
    discount_price DECIMAL(10, 2),
    CHECK (discount_price <= price)
);

The first CHECK ensures price is always positive; the second ensures a discounted price is never higher than the regular price.

A Note on Older MySQL Versions

Common Mistakes

Key Takeaways

Summary

The CHECK constraint moves data validation rules — like "price must be positive" or "age cannot be negative" — directly into the database, so invalid data can never be stored, no matter which application or script is doing the inserting.

In this lesson, you defined CHECK constraints, watched MySQL reject a violating insert, and learned why this behavior only became reliable from MySQL 8.0.16 onward. Next, you'll learn how AUTO_INCREMENT generates unique IDs automatically.