Why Aren't My MySQL CHECK Constraints Working?

Understanding CHECK Constraint Behavior in MySQL

MySQL's handling of CHECK constraints can sometimes be unexpected. A CHECK constraint might seem inactive, even if correctly defined. The key is understanding MySQL version compatibility.

MySQL 8.0.16 was the first version to fully support CHECK constraints. If your MySQL version is older than 8.0.16, the CHECK clause will be parsed but ignored by all storage engines. Data violating the constraint will be inserted without error.

Consult the official MySQL Reference Manual (https://www.php.cn/link/e6c19a87ef9e816e02ce247d4f41d38a) to confirm your MySQL version's capabilities.

Alternatives to CHECK Constraints (for older MySQL versions):

If your MySQL version lacks CHECK constraint support, consider using triggers. Triggers execute custom code in response to database events like row insertion. A trigger can validate data before insertion, ensuring data integrity.

For instance, a trigger to enforce a non-negative value for the SD field in a Customer table could be:

<code class="language-sql">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 ;</code>

This trigger intercepts INSERT statements on the Customer table. If SD is negative, the trigger sets it to zero. This provides similar functionality to a CHECK constraint.

The above is the detailed content of Why Aren't My MySQL CHECK Constraints Working?. For more information, please follow other related articles on the PHP Chinese website!