MySQL CHECK constraint is invalid

Transfer from http://blog.csdn.net/maxint64/article/details/8643288

When I try to use check constraints in MySQL today, we know that check constraints are not valid in MySQL, such as the following code, which adds a check constraint when creating table Table1, requires the value of the Field1 field to be greater than 0, and then inserts 1 into the Field1 field, This obviously violates the check constraint, but this code can be successful in MySQL.

1. CREATE TABLE table1
2. (
3. Field1 INT,
4. CHECK (field1 > 0)
5. );
6. INSERT into table1 VALUES (-1);
7. SELECT * from table1;

Operation Result:

1. +--------+
2. | Field1 |
3. +--------+
4. | -1 |
5. +--------+
6. 1 row in Set (0.00 sec)

There are two ways of solving this problem. If you need to set the field values for a check constraint to be discrete, and you can easily enumerate all possible 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:

CREATE TABLE table1

1. (
2. Gender ENUM (' Male ', ' female ')
3. );
4. INSERT into table1 VALUES (' Sau Kat '); --This insert operation will fail

However, there are some minor differences between the enum () type and the set () type, as stated in the official documentation.

If a field that needs to set a check constraint is contiguous, or if it is difficult to enumerate all the values, such as positive or positive integers, then the data validity can be achieved only with triggers instead of constraints. The following code creates a constraint called Testfield1_beforeinsert, which guarantees that the value of the Field1 field in the newly inserted data is not less than 0.

1. DELIMITER $$
3. CREATE TRIGGER Testfield1_beforeinsert before INSERT on table1
4. For each ROW
5. BEGIN
6. IF new.field1 < 0 Then
7. SET new.field1 = 0;
8. END IF;
9. end$$

MySQL CHECK constraint is invalid