MySQL 8.0 New Features - Introduction to Check Constraints

Preface
Check Constraints

Create, Delete and View
limit

Summarize

Preface

In MySQL 8.0, a very useful new feature is introduced - check constraints, which can improve the control over illegal or unreasonable data writing. Let's take a closer look at it.

Check Constraints

Create, Delete and View

(1) You can create check constraints when creating a table.

mysql> CREATE TABLE t1
 -> (
 -> CHECK (c1 <> c2),
 -> c1 INT CHECK (c1 > 10),
 -> c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),
 -> c3 INT CHECK (c3 < 100),
 -> CONSTRAINT c1_nonzero CHECK (c1 <> 0),
 -> CHECK (c1 > c3)
 -> );
Query OK, 0 rows affected (0.03 sec)

(2) You can also add check constraints using the following statements:

mysql> ALTER TABLE t1 ADD CONSTRAINT c3_nonzero CHECK ((c3<>0));
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

(3) You can delete the check constraint by using the following statement

mysql> ALTER TABLE t1 DROP CONSTRAINT c3_nonzero;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

(4) You can view the check constraints by querying the table structure

mysql> SHOW CREATE TABLE t1_G
*************************** 1. row ***************************
 Table: t1
Create Table: CREATE TABLE `t1` (
 `c1` int DEFAULT NULL,
 `c2` int DEFAULT NULL,
 `c3` int DEFAULT NULL,
 CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)),
 CONSTRAINT `c2_positive` CHECK ((`c2` > 0)),
 CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)),
 CONSTRAINT `t1_chk_2` CHECK ((`c1` > 10)),
 CONSTRAINT `t1_chk_3` CHECK ((`c3` < 100)),
 CONSTRAINT `t1_chk_4` CHECK ((`c1` > `c3`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

(5) You can also view it through the following two views: table_constraints queries which constraints exist in the table, and check_constraints queries the specific definition of the check constraint.

mysql> SELECT * FROM information_schema.table_constraints WHERE table_name='t1';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def | test | c1_nonzero | test | t1 | CHECK | YES |
| def | test | c2_positive | test | t1 | CHECK | YES |
| def | test | t1_chk_1 | test | t1 | CHECK | YES |
| def | test | t1_chk_2 | test | t1 | CHECK | YES |
| def | test | t1_chk_3 | test | t1 | CHECK | YES |
| def | test | t1_chk_4 | test | t1 | CHECK | YES |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM information_schema.check_constraints WHERE constraint_name='c1_nonzero';
+--------------------+-------------------+-----------------+--------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE |
+--------------------+-------------------+-----------------+--------------+
| def | test | c1_nonzero | (`c1` <> 0) |
+--------------------+-------------------+-----------------+--------------+
1 row in set (0.00 sec)

(6) When inserting data that does not meet the check constraint, an error will be reported directly

mysql> insert into t1 values(0,0,0);
ERROR 3819 (HY000): Check constraint 'c1_nonzero' is violated.

limit

(1) Auto-increment columns and columns of other tables do not support check constraints

(2) Uncertain functions, such as CONNECTION_ID(), CURRENT_USER(), NOW(), etc., do not support check constraints.

(3) User-defined functions do not support check constraints

(4) Stored procedures do not support check constraints

(5) Variables do not support check constraints

(6) Subqueries do not support check constraints

Summarize

Check constraints are a very good feature that can realize a variety of data verification scenarios. You can try it.

The above is a brief introduction to the new feature of MySQL 8.0 - check constraints. For more information about the new feature of MySQL 8.0 - check constraints, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in: