How check if the sum of the values of two columns, in the same row have exactly 100 as result in mysql

Question

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!!!

Answer 1

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 ;