MySQL field values, can a float be defined beyond digit count?

Question

Three questions, all basically the same:

So my table has a field, and it's data type is float. There are only 64 possible values, however, ranging from 0.25 to 16.0 (increments of 0.25), and I'd like to only allow those values as an extra layer of validation.

1. Can I set the field to be greater than zero?

2. Can I set a minimum and maximum value for a float field?

3. Can I set the increments in some way?

Or, should I consider:

a) setting it as an integer and multiplying any input by 4 before it goes in, and setting a maximum value of 64.

b) using a really long enum?

Answer 1

This is usually done with a CHECK CONSTRAINT. Unfortunately MySql doesn't support CHECK CONSTRAINT. You can use BEFORE UPDATE TRIGGER instead...

CREATE TRIGGER t BEFORE UPDATE ON yourTable 
FOR EACH ROW BEGIN 
IF NEW.name --here you put a check for a valid value
   SET NEW.name = 1 / 0;      --an error is raised - no insert
END IF;

Answer 2

Typically this sort of thing is enforced in the client application. However, if you search for "server-enforced data integrity" for mysql you'll find some of what you need.

Answer 3

i personally would use DECIMAL, not FLOAT. FLOAT is only an approximation of your value, and your values are really specific.

to enforce specific values, i would create a lookup table with just those 64 values in there and use a foreign key.