CHECK constraint in MySQL is not working

问题:

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.

回答1:

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.

回答2:

Unfortunately MySQL does not support SQL check constraints. You can define them in your DDL query for compatibility reasons but they are just ignored.

There is a simple alternative

You can create BEFORE INSERT and BEFORE UPDATE triggers which either cause an error or set the field to its default value when the requirements of the data are not met.

Example for BEFORE INSERT working after MySQL 5.5

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `Test`
FOR EACH ROW
BEGIN
    IF CHAR_LENGTH( NEW.ID ) < 4 THEN
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT := 'check constraint on Test.ID failed';
    END IF;
END$$   
DELIMITER ;  

Prior to MySQL 5.5 you had to cause an error, e.g. call a undefined procedure.

In both cases this causes an implicit transaction rollback. MySQL does not allow the ROLLBACK statement itself within procedures and triggers.

If you don't want to rollback the transaction ( INSERT / UPDATE should pass even with a failed "check constraint" you can overwrite the value using SET NEW.ID = NULL which will set the id to the fields default value, doesn't really make sense for an id tho

Edit: Removed the stray quote.

Concerning the := operator:

Unlike =, the := operator is never interpreted as a comparison operator. This means you can use := in any valid SQL statement (not just in SET statements) to assign a value to a variable.

https://dev.mysql.com/doc/refman/5.6/en/assignment-operators.html

Concerning backtick identifier quotes:

The identifier quote character is the backtick (“`”)

If the ANSI_QUOTES SQL mode is enabled, it is also permissible to quote identifiers within double quotation marks

http://dev.mysql.com/doc/refman/5.6/en/identifiers.html

回答3:

CHECK constraints are ignored by MySQL as explained in a miniscule comment in the docs: CREATE TABLE

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

回答4:

The CHECK constraint doesn't seem to be implemented in MySQL.

See this bug report: https://bugs.mysql.com/bug.php?id=3464

回答5:

As mentioned by joanq MariaDB now seems to support CHECK constraints among other goodies:

"Support for CHECK CONSTRAINT (MDEV-7563)."

https://mariadb.com/kb/en/mariadb/mariadb-1021-release-notes/

回答6:

try with set sql_mode = 'STRICT_TRANS_TABLES' OR SET sql_mode='STRICT_ALL_TABLES'