I have this problem in mysql (phpmyadmin).. I should want to check if the values of 2 columns do 100… for example I have this table
| BLABLABLA | BLABLABLA | VALUE1 | VALUE2 |
and user want add this values(bla, bla, 20, 30).. I would that 20 and 30 can't be added in this table because 20+30<>100.. my code is:
ALTER TABLE `partita` CHECK (`100` = (SELECT (`POSSESSO_PALLA_CASA`+`POSSESSO_PALLA_OSPITE`) FROM `partita`))
but naturally this was wrong.. how i can do? thank you all!!!
As already commented MySQL doesn't support CHECK
constraint. Per MySQL Documentation it says:
The CHECK clause is parsed but ignored by all storage engines
You should rather use a BEFORE INSERT
trigger as an alternative like below
DELIMITER //
CREATE TRIGGER sumcheck_before_insert
BEFORE INSERT
ON bla_table FOR EACH ROW
BEGIN
IF (NEW.VALUE1 + NEW.VALUE2 <> 100) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'can not insert data';
END IF
END; //
DELIMITER ;