Limiting integer data type field lengths

Question

I am trying to limit the number of numbers that an integer field can contain. For example, I want the field to contain a number no more than 5 long, so 99999 would be the highest valid entry.

Is this possible to do in MySQL? I have looked at the documentation but haven't found my answer.

Answer 1

Unfortunately neither the CHECKconstraint nor user defined types are implemented in MySQL. Maybe this will change in future versions.

Until then you can use a trigger to correct the input if that is a way to go for you:

delimiter //
CREATE TRIGGER trigger_check BEFORE INSERT ON your_table
FOR EACH ROW 
BEGIN 
    IF NEW.NUM > 99999 THEN 
        SET NEW.NUM = 0; 
    END IF; 
END
//

Answer 2

You can add a trigger to enforce this whenever a change is made to the database. The trigger will execute before the change would be applied to verify that it is updating with a "valid" entry. If the entry is invalid, the update can be rejected.