CHECK constraint in MySQL is not working

JohnRaja picture JohnRaja · Jan 22, 2010 · Viewed 103.4k times · Source

First I created a table like

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

and then inserted values in that table

INSERT INTO Customer values ('-2','abc','zz');

MySQL doesn't show an error, it accepted the values.

Answer

David Kerins picture David Kerins · Jan 22, 2010

MySQL 8.0.16 is the first version that supports CHECK constraints.

Read https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

If you use MySQL 8.0.15 or earlier, the MySQL Reference Manual says:

The CHECK clause is parsed but ignored by all storage engines.

Try a trigger...

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 ;

Hope that helps.