How Do MySQL CHECK Constraints Work, and How Can I Ensure Data Validation in Older and Newer Versions?

CHECK constraints in MySQL

When trying to add a CHECK constraint to a MySQL table, you may encounter unexpected behavior when inserting data that violates the constraint. This article will delve into the subtleties of CHECK constraints in MySQL and provide a solution to enforce validation.

In earlier versions of MySQL (until 8.0.15), CHECK constraints were defined but not enforced. As stated in the MySQL Reference Manual: "The CHECK clause is parsed by all storage engines but ignored."

Solutions for MySQL 8.0.15 and earlier

To force data validation in the absence of native CHECK constraint support, you can create a trigger:

DELIMITER //

CREATE TRIGGER trig_sd_check BEFORE INSERT ON Customer
FOR EACH ROW
BEGIN
  IF NEW.SD < 0 THEN
    SET NEW.SD = 0;
  END IF;
END //

DELIMITER ;

This trigger will intercept any INSERT statement and if the value of the SD column is less than 0, it will be modified to 0, ensuring that the constraint is maintained.

CHECK constraints for MySQL 8.0.16 and later

MySQL 8.0.16 introduces full support for CHECK constraints. To define CHECK constraints in 8.0.16 or later, use the following syntax:

CREATE TABLE Customer (
  SD integer CHECK (SD > 0),
  Last_Name varchar (30),
  First_Name varchar(30)
);

MySQL will now enforce constraints and prevent the insertion of data that violates the constraints.

The above is the detailed content of How Do MySQL CHECK Constraints Work, and How Can I Ensure Data Validation in Older and Newer Versions?. For more information, please follow other related articles on the PHP Chinese website!