CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Luke
Luke

Reputation: 291

Limiting integer data type field lengths

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.

Upvotes: 5

Views: 187

Answers (3)

juergen d
juergen d

Reputation: 204854

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
//

Upvotes: 3

Oleksi
Oleksi

Reputation: 13097

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.

Upvotes: 1

Corbin
Corbin

Reputation: 33457

This is not possible to do at a DB level. You'll have to either use stored procedures, or put that logic in your application.

http://dev.mysql.com/doc/refman/5.5/en/integer-types.html

A medium int is the closest you can get.

Also, you'll likely see something like this at some point:

CREATE TABLE blah ( col INT (5) );

This does not do what you think though. This does not actually constrain the length of the field to 5 places. It signifies that it should be 0 padded to 5 places when zerofill is set on the column.

Upvotes: 0

Related Questions