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!
解决方案
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 ;