Lesson 4 of the SQL Optimization Course: Column - level CHECK Constraint

Introduction

For relational databases, the design of tables and SQL is written are particularly crucial. It wouldn’t be an exaggeration to say that they account for 90% of performance. So this time, specifically targeting these two major knowledge areas, we’ll conduct a detailed analysis for you, peeling back the layers.

This Series uses plain and understandable language and selects a large number of examples to elaborate on the subtleties for you.

🧑‍💻 Target audience:

DBA
Database developers
Students

We will use MySQL as the demonstration database.

The CHECK constraint is used to filter one or more columns according to pre - set rules. If the condition is true, the filtering is successful; if false, the filtering fails, and a failure code is returned to the client.

Why is this discussed separately? It’s common to encounter non - standard SQL. In many cases, since the code - side doesn’t perform filtering, the filtering has to be done at the database level.

1. Common Filtering Methods

Suppose we have a table f1 with a column r1 whose value should be a multiple of 3; otherwise, the write operation is rejected. Also, assume that the input for r1 is not standardized and can only be filtered by the database. What can we do? There are mainly the following methods:

1.1 Write a Pre - insert Trigger

Example 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
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 ;

- Execution and Results - Abnormal Insertion:

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

Normal Insertion

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
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 simply addresses the scenario of single - column filtering. Complex multi - column filtering will be discussed later.

1.2 Write a Stored Procedure to Encapsulate SQL

Handle input constraints within the stored procedure. This is similar to handling input constraint logic on the application side, but the same processing logic is placed at the database side. And in the future, all data entry can only rely on the single - entry point of the stored procedure.

1.3 Accept All Inputs and Process Non - standard Data Regularly

This will lead to a large amount of entered data, including many useless non - standard data. Usually, non - standard data is processed regularly during non - peak business hours. Examples for these two methods are not provided as they are similar to the first one.

2. CHECK Constraints

Now, let’s talk about the CHECK constraint that filters at the column level and is defined before the table definition (MySQL version >= 8.0.16).

1
2
3
4
5
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)

The relevant restrictions of the CHECK constraint are as follows:

The constraint name must be unique in each database. That is, there are no two identical constraints in a single database. If not defined, the system will automatically generate a unique constraint name.
The CHECK constraint takes effect for statements like insert, update, replace, load data, load xml; it fails for the corresponding ignore statements.
Not every function can be used. For example, functions with uncertain results such as NOW(), CONNECTION_ID(), CURRENT_USER().
It is not applicable to stored procedures and stored functions.
System variables are not applicable.
Sub - queries are not applicable.
Foreign key actions (such as ON UPDATE, ON DELETE) are not applicable.
By default, enforced is enabled. If not enforced is added separately, the CHECK constraint fails.

Example 2 Based on the above, let’s look at the actual examples of the two tables. The CHECK constraint only takes effect for table f1.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
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)

Next, let’s look at a more detailed example of the CHECK constraint.

Example 3

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
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)

Regarding the above example:

Constraints tb_f1_r1_nonzero, tb_f1_r1r2_chk1, tb_f1_r1r3_chk are not tied to a specific column and are globally valid. In other words, they are table - based CHECK constraints.
Constraint tb_f1_r1_chk1 includes constraint tb_f1_r1_nonezero, so tb_f1_r1_nonezero can never detect an exception. After checking, this constraint can be removed.

The definition after removing the redundant constraint:

1
2
3
4
5
6
7
8
9
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)

Let’s test this table. We can see that the constraints for each column are in an “AND” relationship. If any column’s constraint is not met, the write operation fails.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
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 can transform the trigger we started with. By adding the relevant conditions, we can achieve the same column - level CHECK constraint.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
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 the Effect

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
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)

Summary

This article introduced the usage and some examples related to the database CHECK constraint. Personally, I suggest that if the CHECK constraint can be separated from the database side and implemented on the application side, it would be better. The simpler the database side, the better the performance. However, there are exceptions. If it is difficult to implement on the application side due to historical reasons or other factors, it has to be implemented on the database side.

👋 See you in the next lession.

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.