int - MySql user input restriction -

i new mysql, trying create table store scores ranging 0 5. keep reading constraint check fails, allows me input numbers greater 5.

here sample of script

create table part2(     p2_q1 int(1)     check (p2_q1 >= 0 , p2_q1 < 6),    //this 1 way i've read     p2_q2 int(1)     check (p2_q2 >= 0 , p2_q2 < 6),     p2_q3 int(1)     check (p2_q3 between 0 , 5),       //and other way     p2_q4 int(1)     check (p2_q4 between 0 , 5) ); 

thanks ahead of time can get!

you can define check constraints in mysql has no effect. engine ignores whatever define. may supported in future version of mysql.

but instead can define 2 triggers called on every update , insert. insert trigger:

delimiter | create trigger `ins_p2` before insert on `part2` each row begin    if     new.p2_q1 not between 0 , 5       , new.p2_q2 not between 0 , 5       , new.p2_q3 not between 0 , 5       , new.p2_q4 not between 0 , 5          signal sqlstate '45000' set message_text = 'out of range error';    end if; end | delimiter ; 

and can same updates.