check constraint in mysql not working

Question

First i create a table like CREATE TABLE Customer (SD integer CHECK (SD > 0), Last_Name varchar (30), First_Name varchar(30));

and then insert values in that table INSERT INTO Customer values ('-2','abc','zz');

Its doesn't shows the error.Accept the values in mysql

Answer 1

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

See this bug report: http://bugs.mysql.com/bug.php?id=25660

Answer 2

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.

David Kerins