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?


1 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:

create trigger student_info_bitrig before insert on student_info
for each row
    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;



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

Not the answer you're looking for? Browse other questions tagged or ask your own question.