Want to restrict the value of a MySQL field to specific range (Decimal values)

Question

I want to restrict the value of a field in a row of a table to a specific range. Is it possible to restrict my relationship_level field to [0.00 to 1.00]?

At the moment I am using DECIMAL(2,2), it wouldn't allow DECIMAL(1,2) as M must be >= D. I assume a data type of DECIMAL(2,2) will actually allow values from 00.00 up to 99.99?

CREATE TABLE relationships (
    from_user_id MEDIUMINT UNSIGNED NOT NULL,
    to_user_id MEDIUMINT UNSIGNED NOT NULL,
    relationship_level DECIMAL(2,2) UNSIGNED NOT NULL,
    PRIMARY KEY (from_user_id, to_user_id), 
    FOREIGN KEY (from_user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION,
    FOREIGN KEY (to_user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION,
    INDEX relationship_from_to (to_user_id, from_user_id, relationship_level)
) ENGINE = INNODB;

Is there a better way to do this, can anyone foresee any limitations?

Many thanks!

If you really want to restrict the values from `0.00` to `1.00`, you should use a `DECIMAL(3,2)` datatype and an FK referencing to a "stable" (reference) table, with just 101 rows. Much like the answer to your other question: [Limit the value of a MySQL datatype to a specific range](http://stackoverflow.com/questions/9249695/limit-the-value-of-a-mysql-datatype-to-a-specific-range-preferably-not-enum) — ypercubeᵀᴹ, Mar 05 '12 at 23:46
Perfect. I imagined it would involve something similar to this (with a FK referencing another table restricting the range) I just wondered if there existed a more 'efficient' method to the previous approach. Many thanks. — leokennedy, Mar 05 '12 at 23:49
There is one other possibility but I haven't used it so I'm not aware of any problems. Define an updatable View using the `WITH CHECK OPTION`. But then, you'll have to use only that View instead of the Table (at least for Inserts and Updates): [CREATE VIEW Syntax](http://dev.mysql.com/doc/refman/5.1/en/create-view.html) — ypercubeᵀᴹ, Mar 06 '12 at 00:02

score 3 · Answer 1 · answered Mar 05 '12 at 23:31

3

Actually, DECIMAL(2,2) will allow a decimal of up to 2 places, BOTH of which are allocated to decimal places. The maximum value for that field would be 0.99, and the minimum would be 0.00.

To restrict values to 00.00 to 99.99, use DECIMAL(4,2) UNSIGNED.

answered Mar 05 '12 at 23:31

kbenson

1,464
9
13

Ok, thanks for clarifying the range I would actually get from using DECIMAL(2,2)=[0.00 to 0.99]. Is it actually possible to limit it in such a way to achieve a range of [0.00 to 1.00], I'm assuming it would involve alternatives to the DECIMAL datatype. – leokennedy Mar 05 '12 at 23:34
1

No, you can't have from `[0.00 to 1.00]` restricted. With `DECIMAL(3,2) UNSIGNED` you could have from 0.00 to 9.99 – ypercubeᵀᴹ Mar 05 '12 at 23:37
You could restrict it using BEFORE INSERT/UPDATE TRIGGERS. You could either coerce the values or trigger an error by setting a NOT NULL column to NULL in the trigger. – user1191247 Mar 05 '12 at 23:42

score 3 · Accepted Answer · answered Mar 05 '12 at 23:49

3

You can simulate a check constraint in MySQL using triggers.

For example, if you want to force all values larger than 1.00 to be stored as 1.00, you could do so with 2 triggers like this:

DELIMITER $$

DROP TRIGGER IF EXISTS tr_b_ins_relationships $$

CREATE TRIGGER tr_b_ins_relationships BEFORE INSERT ON relationships FOR EACH ROW BEGIN
  IF new.relationship_level > 1
  THEN
    SET new.relationship_level = 1;
  END IF;
END $$

DELIMITER ;


DELIMITER $$

DROP TRIGGER IF EXISTS tr_b_upd_relationships $$

CREATE TRIGGER tr_b_upd_relationships BEFORE UPDATE ON relationships FOR EACH ROW BEGIN
  IF new.relationship_level > 1
  THEN
    SET new.relationship_level = 1;
  END IF;
END $$

DELIMITER ;

answered Mar 05 '12 at 23:49

Ike Walker

64,401
14
110
109

Triggers sounds excellent, I think I'll be using them to solve this problem and in a few other places now. Thanks! – leokennedy Mar 06 '12 at 00:45
The apple sounded excellent (and looked yummy) to Adam, too. [Are database triggers evil?](http://stackoverflow.com/questions/460316/are-database-triggers-evil) – ypercubeᵀᴹ Mar 06 '12 at 01:28
1

I assert this is a reasonable use of triggers, especially given the fact that MySQL does not support check constraints. – Ike Walker Mar 06 '12 at 01:43

Want to restrict the value of a MySQL field to specific range (Decimal values)

2 Answers2

Linked