Representing a Maximum 1.00 in MySql Decimal syntax

Question

Is it possible to have a SQL syntax that for a column creation that forces the column value to be between 0.00 to 1.00

Example

0.35 --> Correct
2.10 --> Wrong
0.05 --> Correct

Having DECIMAL(3,2) for my column data type allows maximum values of 9.99 as well.

Can I cap the value to be between 0.00 to 1.00 ONLY?

Does this answer your question? [Want to restrict the value of a MySQL field to specific range (Decimal values)](https://stackoverflow.com/questions/9575604/want-to-restrict-the-value-of-a-mysql-field-to-specific-range-decimal-values) — Ivar, Nov 04 '20 at 08:57

score 3 · Accepted Answer · answered Oct 06 '14 at 12:58

3

In MySQL you can define check constraints but they are currently not implemented and have no effects. But you can use a trigger in MySQL for that and check the value and cancel the update/insertion if wrong

delimiter |
CREATE TRIGGER your_update_trigger BEFORE UPDATE ON your_table
FOR EACH ROW
BEGIN
    IF NEW.decimal_column not between 0.0 and 1.0 THEN 
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'An error occurred. Decimal value out of range';
    END IF;
END
|
delimiter ;

answered Oct 06 '14 at 12:58

juergen d

201,996
37
293
362

I think using a UI validation would be a better approach than forcing an trigger. Could save me extra server trip? I am developing a JEE application. – Ayusman Oct 06 '14 at 13:03
1

I think valdidating in the UI is always the better idea. Check for errors right at the source! – juergen d Oct 06 '14 at 13:04

giammin · Answer 2 · 2014-10-07T07:27:20.157

just use a constraint:

ALTER TABLE TABLENAME
ADD CONSTRAINT CK_TABLENAME_COLUMNNAME_BETWEEN01 CHECK (
   COLUMNNAME >= 0 AND COLUMNNAME <= 1
)

-- UPDATE, I did not read that you were referring to mysql... above works only in SQL server------

Unfortunately MySQL does not support SQL check constraints. You can define them for compatibility reasons but they are just ignored.

You have to create 2 trigger BEFORE INSERT and BEFORE UPDATE

DELIMITER $$
CREATE TRIGGER `insert_trigger` BEFORE INSERT ON TABLENAME
FOR EACH ROW
BEGIN
    IF NEW.COLUMNNAME not between 0.0 and 1.0 THEN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Decimal value out of range';
    END IF;
END$$    


CREATE TRIGGER `update_trigger` BEFORE UPDATE ON TABLENAME
FOR EACH ROW
BEGIN
    IF NEW.COLUMNNAME not between 0.0 and 1.0 THEN 
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Decimal value out of range';
    END IF;
END$$
DELIMITER ;

The signal state should not be random. See the classes here: http://dev.mysql.com/doc/refman/5.5/en/signal.html#signal-condition-information-items — juergen d, Oct 06 '14 at 18:19
please explain the downvote so I can improve my answer, thanks — giammin, Oct 07 '14 at 08:28

Representing a Maximum 1.00 in MySql Decimal syntax

2 Answers2