check constraint means to filter one or more columns according to certain preset rules. If the condition is true, the filtering succeeds; if the condition is false, the filtering fails and the failure code is returned to the client.

Why do you want to list and write this separately? You often encounter irregular SQL. In many cases, you can only filter by the database layer. If you don't filter on the code side, you can only filter on the database side.

1, Common filtering methods

Assume table f1, field r1 is a multiple of 3, otherwise write is rejected. And suppose that the input of r1 is not standardized and can only be filtered by the database, what should we do? There are only a few:

1) Write pre trigger

Example 1

mysql> create table f1 (r1 int);
Query OK, 0 rows affected (0.03 sec)

DELIMITER $

USE `ytt`$

DROP TRIGGER /*!50032 IF EXISTS */ `tr_check_f1_r1`$

CREATE
/*!50017 DEFINER = 'root'@'%' */
TRIGGER `tr_check_f1_r1` BEFORE INSERT ON `f1
FOR EACH ROW BEGIN
IF MOD(new.r1,3) <> 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Column r1 should be mod by 3,failed to insert.';
END IF;
END;
$
DELIMITER ;

Under execution, exceptions are exposed

mysql> insert into f1 values (5);
ERROR 1644 (45000): Column r1 should be mod by 3,failed to insert.

Normal insertion

mysql> insert into f1 values (3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from f1;
+------+
| r1   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

The above example is simple for the scenario of single column filtering, and the complex filtering of multiple columns will be discussed later.

2) Write stored procedure encapsulate SQL

Processing input constraint in stored procedure is the same as processing input constraint logic in program side, but the same processing logic is placed in database side, and all data input in the future can only rely on a single entry of stored procedure.

3) Do not reject any input, and regularly process irregular data

This will lead to a large amount of data input, and there are many useless irregular data. Generally, irregular data is processed regularly during non business peak hours.

2, CHECK constraint

Now we want to talk about the CHECK constraint that has been standardized before the table based definition filtered at the column level. (MySQL version > = 8.0.16)

mysql> create table f1 (r1 int constraint tb_f1_r1_chk1 check (mod(r1,3)=0));
Query OK, 0 rows affected (0.03 sec)

mysql> create table f2 (r1 int constraint tb_f2_r1_chk1 check (mod(r1,3)=0) not enforced);
Query OK, 0 rows affected (0.02 sec)

Here, the related restrictions of CHECK constraint are as follows:

1. constraint names are unique in each database.

In other words, there are not two identical constraints in a single database. If they are not defined, the system will automatically generate a unique constraint name.

1. The check constraint is valid for the statement insert/update/replace/load data/load xml; it is invalid for the corresponding ignore statement.

2. Not every function can be used. For example, if the result of the function is uncertain: NOW(), connection ﹣ ID (), current ﹣ user().

3. Not for stored procedures and stored functions.

4. System variable is not applicable.

5. Subqueries are not applicable.

6. Foreign key actions (such as ON UPDATE, ON DELETE) are not applicable.

7. Enforced is enabled by default. If not enforced is added separately, the check constraint fails.

Example 2

In combination with the actual examples of the two tables above, the check constraint is only valid for table f1.

mysql> insert into f1 values (10);
ERROR 3819 (HY000): Check constraint 'tb_f1_r1_chk1' is violated.
mysql> insert into f2 values (10);
Query OK, 1 row affected (0.01 sec)

mysql> select * from f1
Empty set (0.00 sec)

mysql> select  * from f2;
+------+
| r1   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

Let's look at a more detailed example of the CHECK constraint.

Example 3

mysql> drop table f1;
Query OK, 0 rows affected (0.02 sec)

mysql> create table f1
-> (
->  r1 int constraint tb_f1_r1_chk1 check (r1 > 10),
->  r2 int constraint tb_f1_r2_positive check (r2 > 0),
->  r3 int constraint tb_f1_r3_chk1 check (r3 < 100),
->  constraint tb_f1_r1_nonzero check (r1 <> 0),
->  constraint tb_f1_r1r2_chk1 check (r1 <> r2),
->  constraint tb_f1_r1r3_chk1 check (r1 > r3)
-> );
Query OK, 0 rows affected (0.02 sec)

There is a point in the above example,

1. The constraints TB ﹣ F1 ﹣ R1 ﹣ nonzero, TB ﹣ F1 ﹣ R1 ﹣ R2 ﹣ CHK1, TB ﹣ F1 ﹣ R3 ﹣ chk do not follow the fixed column, which is effective for the whole, or the table based check constraint.

2. Constraint TB ﹣ F1 ﹣ R1 ﹣ contains constraint TB ﹣ F1 ﹣ R1 ﹣ nonezero, so that TB ﹣ F1 ﹣ R1 ﹣ nonezero will never detect an exception. So after checking, remove this constraint.

The definition after removing redundant constraints,

mysql> create table f1
-> (
->  r1 int constraint tb_f1_r1_chk1 check (r1 > 10),
->  r2 int constraint tb_f1_r2_positive check (r2 > 0),
->  r3 int constraint tb_f1_r3_chk1 check (r3 < 100),
->  constraint tb_f1_r1r2_chk1 check (r1 <> r2),
->  constraint tb_f1_r1r3_chk1 check (r1 > r3)
-> );
Query OK, 0 rows affected (0.02 sec)

If you want to do a test on this table, you can see that the constraint of each column here is actually the relationship between and. If any column constraint is not established, the write fails.

mysql> insert into f1 values (20,10,10);
Query OK, 1 row affected (0.01 sec)

mysql> insert into f1 values (10,10,10);
ERROR 3819 (HY000): Check constraint 'tb_f1_r1_chk1' is violated.
mysql> insert into f1 values (20,-10,10);
ERROR 3819 (HY000): Check constraint 'tb_f1_r2_positive' is violated.
mysql> insert into f1 values (20,10,30);
ERROR 3819 (HY000): Check constraint 'tb_f1_r1r3_chk1' is violated.

Next, we modify the trigger at the beginning. As long as we add the relevant conditions, we can implement the same check column constraint.

DELIMITER $

USE `ytt`$

DROP TRIGGER /*!50032 IF EXISTS */ `tr_check_f1_r1`$

CREATE
/*!50017 DEFINER = 'root'@'%' */
TRIGGER `tr_check_f1_r1` BEFORE INSERT ON `f1`
FOR EACH ROW BEGIN
DECLARE v1 TINYINT DEFAULT 0;

IF (new.r1 > 10 AND new.r1 > new.r3 AND new.r1 <> new.r2 AND new.r2 > 0 AND new.r3 < 100) = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "Failed to write: constraint check: \n (\n r1 >10 \n&&  r1 > r3 \n&& r1 <> r2 \n&& r2> 0 \n&& r3 < 100\n).";
END IF;
END;
$

DELIMITER ;

Test results,

mysql> insert into f1 values (20,30,100);
ERROR 1644 (45000): Failed to write: constraint check:
(
r1 >10
&&  r1 > r3
&& r1 <> r2
&& r2> 0
&& r3 < 100
).

mysql> insert into f1 values (100,30,90);
Query OK, 1 row affected (0.01 sec)

mysql> select * from f1;
+------+------+------+
| r1   | r2   | r3   |
+------+------+------+
|  100 |   30 |   90 |
+------+------+------+
1 row in set (0.00 sec)

conclusion

This paper introduces the usage of database CHECK constraint and some examples.

I personally suggest that the best way to implement this CHECK constraint is to split it from the database side to the application side. The simpler the data side, the better the performance. But there are exceptions. If the application side is difficult to implement due to historical reasons or other factors, it can only be sent to the database side.

What else do you want to know about MySQL technology? Leave a message and tell Xiaobian!