How do I limit an "Age" column to two integers?

[Granpoh]

Hi,

In my table, I have a field for "Age". I'm trying to limit the input to 2 integers, so that the max age anyone can choose is 99 years old. Under "Length/Value", I chose "2", yet I'm still able to make someone over 1,000,000 years old. How do I fix this?

Thanks for all assistance

[Nobbie]

>How do I fix this?

You cannot simply fix that. The Length Attribut of Integer Variables does not effect the internal storage (the storage of standard INT is a binary 4-byte integer range between -2147483648 and 2147483647, SMALLINT uses 2-bytes range between -32768 and 32767, TINYINT is 1-byte range between -127 and 127), but only the representation in print layouts.

If you like to limit the Age to 99, you must either write a little check routine in the corresponding script (PHP?), which checks the value before inserting it, or you must create a trigger which sets the Age to 99 for the new inserted record, if Age is greater than 99.

Example:

Code: Select all

CREATE TRIGGER age_check AFTER INSERT ON mytable
FOR EACH ROW SET NEW.Age = LEAST(NEW.Age,99);


Not tested...