Skip to content

Latest commit

 

History

History
59 lines (48 loc) · 2.21 KB

information-schema-tidb-check-constraints.md

File metadata and controls

59 lines (48 loc) · 2.21 KB
Raw
title summary
TIDB_CHECK_CONSTRAINTS
Learn the `TIDB_CHECK_CONSTRAINTS` INFORMATION_SCHEMA table.

TIDB_CHECK_CONSTRAINTS

The TIDB_CHECK_CONSTRAINTS table provides information about CHECK constraints on tables. In addition to the columns in CHECK_CONSTRAINTS, TIDB_CHECK_CONSTRAINTS provides the name and ID of the table that defines the CHECK constraint.

USE INFORMATION_SCHEMA;
DESC TIDB_CHECK_CONSTRAINTS;

The output is as follows:

+--------------------+-------------+------+------+---------+-------+
| Field              | Type        | Null | Key  | Default | Extra |
+--------------------+-------------+------+------+---------+-------+
| CONSTRAINT_CATALOG | varchar(64) | NO   |      | NULL    |       |
| CONSTRAINT_SCHEMA  | varchar(64) | NO   |      | NULL    |       |
| CONSTRAINT_NAME    | varchar(64) | NO   |      | NULL    |       |
| CHECK_CLAUSE       | longtext    | NO   |      | NULL    |       |
| TABLE_NAME         | varchar(64) | YES  |      | NULL    |       |
| TABLE_ID           | bigint(21)  | YES  |      | NULL    |       |
+--------------------+-------------+------+------+---------+-------+
6 rows in set (0.00 sec)

The following example adds a CHECK constraint using the CREATE TABLE statement:

SET GLOBAL tidb_enable_check_constraint = ON;
CREATE TABLE test.t1 (id INT PRIMARY KEY, CHECK (id%2 = 0));
SELECT * FROM TIDB_CHECK_CONSTRAINTS\G

The output is as follows:

*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: test
   CONSTRAINT_NAME: t1_chk_1
      CHECK_CLAUSE: (`id` % 2 = 0)
        TABLE_NAME: t1
          TABLE_ID: 107
1 row in set (0.02 sec)

Fields in the TIDB_CHECK_CONSTRAINTS table are described as follows:

  • CONSTRAINT_CATALOG: The catalog of the constraint, which is always def.
  • CONSTRAINT_SCHEMA: The schema of the constraint.
  • CONSTRAINT_NAME: The name of the constraint.
  • CHECK_CLAUSE: The clause of the check constraint.
  • TABLE_NAME: The name of the table where the constraint is located.
  • TABLE_ID: The ID of the table where the constraint is located.