CODEWITHSUNDEEP
mysqlmysql-workbenchsqldatatypes
Ayusman
Ayusman

Reputation: 8719

Representing a Maximum 1.00 in MySql Decimal syntax

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?

Upvotes: 0

Views: 315

Answers (2)

giammin
giammin

Reputation: 18958

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 ;

Upvotes: 0

juergen d
juergen d

Reputation: 204766

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 ;

Upvotes: 3

Related Questions