CHECK Constraints in MySQL0%

CHECK Constraints in MySQL

Beginner12 min read•Updated: 2026-09-12

CHECK Constraints: Enforcing Custom Business Logic

Data types ensure that numbers are stored in numeric columns and dates are stored in temporal columns. However, data types cannot enforce business rules such as: "Employee salary must be greater than ₹10,000", "Product discount percentage must be between 0 and 100", or "End date must be after Start date". To validate these business rules directly inside the database engine, you use CHECK Constraints.

1. What is a CHECK Constraint?

A CHECK constraint specifies a boolean expression that must evaluate to TRUE (or UNKNOWN) for every inserted or updated row. If a statement causes the condition to evaluate to FALSE, MySQL rejects the operation with error 3819: Check constraint is violated.

[!IMPORTANT] MySQL Version Note: Older versions of MySQL (MySQL 5.7 and earlier) parsed CHECK constraints syntactically but silently ignored them! Active enforcement of CHECK constraints was officially implemented in MySQL 8.0.16.

2. Defining CHECK Constraints in SQL

1CREATE TABLE product_discounts (
  discount_id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(150) NOT NULL,
  base_price DECIMAL(10, 2) NOT NULL,
  discount_percentage DECIMAL(5, 2) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  
  -- Constraint 1: Base price must be strictly positive
  CONSTRAINT chk_positive_price CHECK (base_price > 0),
  
  -- Constraint 2: Discount must be between 0% and 100%
  CONSTRAINT chk_discount_range CHECK (discount_percentage >= 0.00 AND discount_percentage <= 100.00),
  
  -- Constraint 3: Multi-column validation (End date must be after Start date)
  CONSTRAINT chk_valid_date_range CHECK (end_date >= start_date)
17);

3. Testing Violation Errors

Let us test our constraint by attempting to insert an illegal discount:

1-- Attempting to insert a negative price and a 150% discount:
2INSERT INTO product_discounts (product_name, base_price, discount_percentage, start_date, end_date)
3VALUES ('Gaming Mouse', -500.00, 150.00, '2026-03-01', '2026-03-10');

MySQL Server Response:

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

The database rejected the bad data at the door, safeguarding your application against corrupted records!

4. Complex Expressions with CHECK

You can use standard SQL operators, boolean logic, and scalar functions within CHECK expressions:

1CREATE TABLE staff_members (
  staff_id INT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  age INT NOT NULL,
  
  -- Check age requirement (At least 18 years old):
  CONSTRAINT chk_minimum_age CHECK (age >= 18),
  
  -- Check email format contains '@' and '.':
  CONSTRAINT chk_email_format CHECK (email LIKE '%@%.%')
12);

5. Adding and Dropping CHECK Constraints

1-- Add CHECK constraint to existing table:
2ALTER TABLE staff_members
3ADD CONSTRAINT chk_staff_age_cap CHECK (age <= 70);
4 
5-- Drop a CHECK constraint in MySQL 8.0+:
6ALTER TABLE staff_members
7DROP CHECK chk_staff_age_cap;

6. Best Practices & Common Pitfalls

Do Not Rely Exclusively on Application-Level Validation: Application code can have bugs, and backend APIs can be bypassed via direct database migrations or CSV imports. Enforcing constraints in the database ensures 100% data integrity regardless of where the data originates.
Handling NULL in CHECK Constraints: In SQL, if a CHECK expression evaluates to UNKNOWN (due to a NULL column), MySQL considers the check satisfied and allows the insert! If a value must not be NULL, always pair your CHECK constraint with an explicit NOT NULL declaration.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Starting with which version did MySQL begin actively enforcing CHECK constraints?

Question 2

What happens when a user attempts to insert a record that causes a CHECK constraint expression to evaluate to FALSE?

Question 3

Which CHECK constraint syntax validates that an end_date column is strictly on or after a start_date column?

Question 4

How does a CHECK constraint behave if the expression evaluates to UNKNOWN due to a NULL value?

Question 5

How do you remove a check constraint named chk_salary from a table named employees in MySQL 8.0+?

Next Lesson

Inserting Records with INSERT INTO

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
UNIQUE Constraint & Candidate Keys	Inserting Records with INSERT INTO

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Inserting Records with INSERT INTO

CHECK Constraints in MySQL

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

CHECK Constraints: Enforcing Custom Business Logic

1. What is a CHECK Constraint?

2. Defining CHECK Constraints in SQL

1CREATE TABLE product_discounts (
  discount_id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(150) NOT NULL,
  base_price DECIMAL(10, 2) NOT NULL,
  discount_percentage DECIMAL(5, 2) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  
  -- Constraint 1: Base price must be strictly positive
  CONSTRAINT chk_positive_price CHECK (base_price > 0),
  
  -- Constraint 2: Discount must be between 0% and 100%
  CONSTRAINT chk_discount_range CHECK (discount_percentage >= 0.00 AND discount_percentage <= 100.00),
  
  -- Constraint 3: Multi-column validation (End date must be after Start date)
  CONSTRAINT chk_valid_date_range CHECK (end_date >= start_date)
17);

3. Testing Violation Errors

Let us test our constraint by attempting to insert an illegal discount:

1-- Attempting to insert a negative price and a 150% discount:
2INSERT INTO product_discounts (product_name, base_price, discount_percentage, start_date, end_date)
3VALUES ('Gaming Mouse', -500.00, 150.00, '2026-03-01', '2026-03-10');

MySQL Server Response:

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

The database rejected the bad data at the door, safeguarding your application against corrupted records!

4. Complex Expressions with CHECK

You can use standard SQL operators, boolean logic, and scalar functions within CHECK expressions:

1CREATE TABLE staff_members (
  staff_id INT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  age INT NOT NULL,
  
  -- Check age requirement (At least 18 years old):
  CONSTRAINT chk_minimum_age CHECK (age >= 18),
  
  -- Check email format contains '@' and '.':
  CONSTRAINT chk_email_format CHECK (email LIKE '%@%.%')
12);

5. Adding and Dropping CHECK Constraints

1-- Add CHECK constraint to existing table:
2ALTER TABLE staff_members
3ADD CONSTRAINT chk_staff_age_cap CHECK (age <= 70);
4 
5-- Drop a CHECK constraint in MySQL 8.0+:
6ALTER TABLE staff_members
7DROP CHECK chk_staff_age_cap;

6. Best Practices & Common Pitfalls

Do Not Rely Exclusively on Application-Level Validation: Application code can have bugs, and backend APIs can be bypassed via direct database migrations or CSV imports. Enforcing constraints in the database ensures 100% data integrity regardless of where the data originates.
Handling NULL in CHECK Constraints: In SQL, if a CHECK expression evaluates to UNKNOWN (due to a NULL column), MySQL considers the check satisfied and allows the insert! If a value must not be NULL, always pair your CHECK constraint with an explicit NOT NULL declaration.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Starting with which version did MySQL begin actively enforcing CHECK constraints?

Question 2

What happens when a user attempts to insert a record that causes a CHECK constraint expression to evaluate to FALSE?

Question 3

Which CHECK constraint syntax validates that an end_date column is strictly on or after a start_date column?

Question 4

How does a CHECK constraint behave if the expression evaluates to UNKNOWN due to a NULL value?

Question 5

How do you remove a check constraint named chk_salary from a table named employees in MySQL 8.0+?

Next Lesson

Inserting Records with INSERT INTO

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
UNIQUE Constraint & Candidate Keys	Inserting Records with INSERT INTO

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next