Why Do MySQL CHECK Constraints Fail in Versions Before 8.0.16?

MySQL CHECK constraint failure problem

When defining database tables, it is important to add constraints to ensure data integrity. CHECK constraints allow you to specify conditions that the values ​​of a specific column must meet. However, in MySQL 8.0.15 and earlier, CHECK constraints are not enforced, which is a special case.

Example:

<code class="language-sql">CREATE TABLE Customer (
  SD integer CHECK (SD > 0),
  Last_Name varchar (30),
  First_Name varchar(30)
);</code>

After creating this table, try to insert rows with negative SD values:

<code class="language-sql">INSERT INTO Customer values ('-2', 'abc', 'zz');</code>

MySQL will not report an error, even if the inserted value violates the specified CHECK constraint. This is because according to the MySQL Reference Manual:

<code>CHECK子句会被解析，但所有存储引擎都会忽略它。</code>

Solution:

To resolve this issue, you can do one of the following:

• Upgrade to MySQL 8.0.16 or higher: This version starts to support CHECK constraints. See the MySQL documentation for more details.
• Implement triggers: Triggers can be used to force constraints that the storage engine ignores. Creating a trigger that checks the SD value before inserting, as shown below, can effectively enforce the constraint:

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

Please note that the above trigger example sets the SD value of negative numbers to 0, you can modify the trigger logic according to your actual needs.

The above is the detailed content of Why Do MySQL CHECK Constraints Fail in Versions Before 8.0.16?. For more information, please follow other related articles on the PHP Chinese website!