MYSQL Tutorial

MySQL CHECK() Constraint

The CHECK constraint validates that values in a column satisfy a Boolean expression. Introduced in MySQL 8.0.16+, it helps enforce custom business rules at the database level.

Syntax

column_name data_type 
  CHECK ( boolean_expression )
  

Defining CHECK in CREATE TABLE

CREATE TABLE accounts (
  account_id INT AUTO_INCREMENT PRIMARY KEY,
  balance    DECIMAL(10,2) 
    CHECK (balance >= 0),
  status     VARCHAR(10) 
    CHECK (status IN ('active','inactive','closed'))
);
  

Adding a CHECK to an Existing Table

ALTER TABLE users
  ADD CONSTRAINT chk_age 
    CHECK (age BETWEEN 18 AND 99);
  

Dropping a CHECK Constraint

ALTER TABLE accounts
  DROP CHECK chk_balance;
  

Common Use Cases

• Ensuring numeric ranges (e.g., quantity >= 0).
• Restricting status values (IN list).
• Validating date logic (e.g., end_date >= start_date).
• Enforcing format with REGEXP (MySQL 8.0+).