mysql check constraint not working [duplicate]

Question

I have used the below query with a constraint to check the values which are inserting to the stu_code column start with 's'.

mysql> CREATE TABLE student_info(
       no INT(3) PRIMARY KEY,
       stu_code VARCHAR(10),
       CHECK (stu_code like 's%'));

Query OK, 0 rows affected (2.68 sec)

but it does not work. When I inserting the values (like below) I can insert values which are not starting with 's'.

mysql> INSERT INTO student_info VALUES (1,'k001');
Query OK, 1 rows affected (0.47 sec)

I am beginer with SQL, Can anyone help me find out what is the issue?

Answer 1

MySQL only parses the text of a CHECK constraint, it does not actually enforce them.

From the documentation:

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

A workaround is to use a trigger:

DELIMITER //
create trigger student_info_bitrig before insert on student_info
for each row
begin
    declare errormessage varchar(100);
    if new.stu_code not like 's%' then
        set errormessage = concat('Error - Value not allowed: ', new.stu_code);
        signal sqlstate '45000' set message_text = errormessage;
    end if;
end
//

DELIMITER ;

Test:

mysql> INSERT INTO student_info VALUES (1,'k001');
ERROR 1644 (45000): Error - Value not allowed: k001
mysql>