-1

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.

3koozy
  • 1
  • 1
  • Check MySQL documentation: `The CHECK clause is parsed but ignored by all storage engines.` [13.1.17 CREATE TABLE Syntax](http://dev.mysql.com/doc/refman/5.6/en/create-table.html). – wchiquito Jun 15 '15 at 09:53
  • that question been asked like 5 years ago.. you would think they solve it by now.. – 3koozy Jun 15 '15 at 09:55
  • The feature request was required several years ago. [Bug #3464 Constraints: support CHECK](http://bugs.mysql.com/bug.php?id=3464). – wchiquito Jun 15 '15 at 10:21

1 Answers1

0

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 ;
juergen d
  • 201,996
  • 37
  • 293
  • 362