i'm using mysql phpmyadmin. i'm looking way put constraint on table that:
column1(int) + column2(int) + column3(int) <= 100
in mysql of can not have such constraints in table definition. may use application layer validate this. if want have using mysql can use trigger , raise error using signal https://dev.mysql.com/doc/refman/5.5/en/signal.html
here how trigger like
delimiter // create trigger check_col_sum before insert on test each row begin if (new.column1+new.column2+new.column3) > 100 signal sqlstate '45000' set message_text = 'sum of column1,column2 , column3 must less equal 100'; end if; end;// delimiter ; test case
mysql> create table test (id int auto_increment primary key, column1 int, column2 int, column3 int); query ok, 0 rows affected (0.14 sec) mysql> delimiter // mysql> create trigger check_col_sum before insert on test -> each row -> begin -> if (new.column1+new.column2+new.column3) > 100 -> signal sqlstate '45000' set message_text = 'sum of column1,column2 , column3 must less equal 100'; -> end if; -> end;// query ok, 0 rows affected (0.09 sec) mysql> mysql> delimiter ; mysql> insert test (column1,column2,column3) values (10,20,30); query ok, 1 row affected (0.08 sec) mysql> select * test ; +----+---------+---------+---------+ | id | column1 | column2 | column3 | +----+---------+---------+---------+ | 1 | 10 | 20 | 30 | +----+---------+---------+---------+ 1 row in set (0.00 sec) mysql> insert test (column1,column2,column3) values (10,20,80); error 1644 (45000): sum of column1,column2 , column3 must less equal 100 mysql> select * test ; +----+---------+---------+---------+ | id | column1 | column2 | column3 | +----+---------+---------+---------+ | 1 | 10 | 20 | 30 | +----+---------+---------+---------+ 1 row in set (0.00 sec) 
Comments
Post a Comment