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!

Answer 1

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 ;

Answer 2

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.