Check constraint with MySQL

Question

I have an issue with MySQL with the check constraint doesn't work... this is the query I entered:

create table courses(cid int(5) primary key,no_s int(2) check(no_s>=0 AND no_s<=5));

so that each course will have 5 students max(just an example).. but when I enter the following query:

insert into courses values(1,7);

MySQL accept the query and executed it, although I specified to check that the number between 0 and 5. with a quick searching in Google I found that it could have some thing to do with MySQL modes but couldn't make that work. also you need to know that I'm using Wamp server 2.5 with MySQL 5.6.17.

Answer 1

MySQL does currently not support check constraints.

It accepts the input but ignores them.

You can use a trigger that cancels the insertion/update on certain conditions like this

delimiter //
CREATE TRIGGER check_trigger BEFORE UPDATE ON your_table
FOR EACH ROW
BEGIN        
    IF (NEW.no_s not between 0 and 5) THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'check failed';
    END IF;
END
//
delimiter ;