MySql user input restriction

Question

I am new to MySql, I am trying to create a table which will store scores ranging from 0 to 5. I keep reading about the constraint CHECK but it fails, it allows me to input numbers greater than 5.

Here is a sample of my script

create table part2(
    P2_Q1 int(1)
    check (P2_Q1 >= 0 and P2_Q1 < 6),    //this is one way I've read
    P2_Q2 int(1)
    check (P2_Q2 >= 0 and P2_Q2 < 6),
    P2_Q3 int(1)
    check (P2_Q3 between 0 and 5),       //and this is the other way
    P2_Q4 int(1)
    check (P2_Q4 between 0 and 5)
);

Thanks ahead of time for any help I can get!

You can define check constraints in MySQL but it has no effect. The engine just ignores whatever you define. It may be supported in a future version of MySQL. — juergen d
– juergen d, Commented Jun 26, 2014 at 0:38

juergen d · Accepted Answer · 2014-06-26 00:42:29Z

1

You can define check constraints in MySQL but it has no effect. The engine just ignores whatever you define. It may be supported in a future version of MySQL.

But instead you can define 2 triggers that will be called on every update and insert. Insert trigger:

delimiter |
CREATE TRIGGER `ins_p2` BEFORE INSERT ON `part2`
FOR EACH ROW BEGIN
   IF     NEW.P2_Q1 not between 0 and 5
      and NEW.P2_Q2 not between 0 and 5
      and NEW.P2_Q3 not between 0 and 5
      and NEW.P2_Q4 not between 0 and 5
   THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'out of range error';
   END IF;
END
|
delimiter ;

And you can do the same for updates.

answered Jun 26, 2014 at 0:42

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Slider Over a year ago

Ok, what would be the script to be written while creating the table? By the way, I ran the script you gave me and it works, thank you.

juergen d Over a year ago

You can't integrate that in the table create script. You have to run this as it is - a seperate statement.

Slider Over a year ago

Thank you, I've been stuck for hours!!

Collectives™ on Stack Overflow

MySql user input restriction

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related