Check Constraint in MySQL 8: Definition and Usage

A CHECK constraint in MySQL 8 is a rule that limits the values allowed in a column to ensure data validity. It automatically rejects any data that does not meet the specified condition when inserting or updating rows.

How It Works

A CHECK constraint acts like a gatekeeper for your database table. Imagine you have a form where users enter their age, and you want to make sure the age is always a positive number. The CHECK constraint sets a rule that only allows values that meet this condition.

When you try to add or change data, MySQL checks the value against the rule. If the value breaks the rule, MySQL stops the action and shows an error. This helps keep your data clean and reliable without needing extra code.

Example

This example creates a table with a CHECK constraint to ensure the age column only accepts values 18 or older.

sql

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

-- Insert valid data
INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25);

-- Insert invalid data (will fail)
INSERT INTO users (id, name, age) VALUES (2, 'Bob', 16);

Output

Query OK, 0 rows affected (for table creation and first insert)

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

When to Use

Use CHECK constraints when you want to enforce simple rules on your data directly in the database. This is useful for things like:

• Ensuring numbers fall within a valid range (e.g., age, price, quantity)
• Restricting values to a specific set (e.g., status codes, categories)
• Validating dates or logical conditions (e.g., start date before end date)

By using CHECK constraints, you reduce errors and keep your data trustworthy without extra programming.

Key Points

• CHECK constraints enforce rules on column values automatically.
• They help maintain data integrity by preventing invalid data.
• MySQL 8.0.16 and later supports CHECK constraints natively, unlike older versions.
• Constraints are checked on INSERT and UPDATE operations.
• Violating a CHECK constraint causes the operation to fail with an error.

Key Takeaways

A CHECK constraint enforces rules on column values to keep data valid.

MySQL 8 supports CHECK constraints natively for automatic data validation.

Use CHECK constraints to prevent invalid data on insert or update.

Violating a CHECK constraint causes an error and stops the data change.

CHECK constraints simplify data integrity without extra application code.