MySQL Workaround for invalid check constraint

First look at the following MySQL operation, I created a table containing A and B, where a check constraint must be greater than 0, but I inserted a ( -2,1,1) of the data, where a=-2, also successfully inserted.

So MySQL is just check, but check is not mandatory.

1Mysql> Create TableCheckdemotable (AintBintIdint,Primary Key(ID));2Query OK,0rows Affected3 4Mysql> Alter TableCheckdemotableAdd constraintCheckdemoconstraintCheck(age>0);5Query OK,0rows Affected6Records:0Duplicates:0Warnings:07 8Mysql> Insert  intoCheckdemotableValues(-2,1,1);9Query OK,1Row AffectedTen  OneMysql> Select *  fromcheckdemotable; A +----+---+----+ - |A|B|Id| - +----+---+----+ the | -2 | 1 |  1 | - +----+---+----+ - 1Rowinch Set

There are two ways to solve this problem:

1. If a field with a check constraint needs to be set to a small range, and it is easier to enumerate all the values, consider setting the type of the field to enum () or collection type set (). For example, a gender field can be set, and an operation that inserts a value other than an enumeration value will not be allowed.

1Mysql> Create TableCheckdemotable (a enum ('male','female'), bintIdint,Primary Key(ID));2Query OK,0rows Affected3 4Mysql> Insert  intoCheckdemotableValues('male',1,1);5Query OK,1Row Affected6 7Mysql> Select *  fromcheckdemotable;8 +----+---+----+9 |A|B|Id|Ten +----+---+----+ One |Man| 1 |  1 | A +----+---+----+ - 1Rowinch Set

2. If you need to set a check constraint in a wide range of fields, and it is difficult to enumerate all the values, such as the value of >0, you can use triggers instead of constraints to achieve the validity of the data. The following code can guarantee the a>0.

1Mysql> Create TableCheckdemotable (AintBintIdint,Primary Key(ID));2Query OK,0rows Affected3 4Mysql>Delimiter||  5 Drop Trigger if existsChecktrigger||  6 Create TriggerChecktrigger beforeInsert  onCheckdemotable forEach row7 begin  8 ifNew.a<=0  Then SetNew.a=1;End if;9 End||  Ten delimiter; One    AQuery OK,0rows Affected -Query OK,0rows Affected -  theMysql> Insert  intoCheckdemotableValues(-1,1,1); -Query OK,1Row Affected -  -Mysql> Select *  fromcheckdemotable; + +---+---+----+ - |A|B|Id| + +---+---+----+ A | 1 | 1 |  1 | at +---+---+----+ - 1Rowinch Set

MySQL Workaround for invalid check constraint