MySQL CHECK Constraint

The CHECK constraint in MySQL ensures that values in a column meet specific conditions before inserting or updating data. It helps maintain data integrity by restricting invalid data entry.

1. MySQL CHECK Constraint Syntax

A. Adding CHECK Constraint in CREATE TABLE

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

✅ Ensures age is always 18 or higher.

B. Adding CHECK Constraint to an Existing Table

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

✅ Applies a CHECK constraint to an existing column.

2. Example – Using Multiple CHECK Constraints

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

✅ Ensures price is positive and stock is not negative.

3. Using Named CHECK Constraints

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    quantity INT,
    CONSTRAINT chk_quantity CHECK (quantity > 0)
);

✅ Naming constraints makes debugging easier.

4. MySQL CHECK Constraint Limitations

🔹 MySQL ignores CHECK constraints in versions before 8.0.16.
🔹 Only supported in MySQL 8.0.16+ for InnoDB tables.
🔹 For older versions, use BEFORE INSERT/UPDATE triggers instead.

Alternative: Enforcing CHECK Using Triggers

DELIMITER //
CREATE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.age < 18 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Age must be at least 18';
    END IF;
END;
//
DELIMITER ;

✅ Mimics CHECK constraint using a trigger for MySQL versions below 8.0.16.

5. Checking Constraints in MySQL

Use the INFORMATION_SCHEMA.TABLE_CONSTRAINTS query:

SELECT CONSTRAINT_NAME, TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_TYPE = 'CHECK';

✅ Lists all CHECK constraints in the database.

6. Dropping a CHECK Constraint

ALTER TABLE employees DROP CHECK chk_age;

✅ Removes the chk_age CHECK constraint.

7. Conclusion

CHECK constraints enforce rules on column values.
Available in MySQL 8.0.16+ (use triggers for older versions).
Use ALTER TABLE to add or remove CHECK constraints.

🚀 Ensure data integrity with CHECK constraints in MySQL!

Souy Soeng

MySQL CHECK Constraint