Support TABLE_NAME in CHECK_CONSTRAINTS

[shawn0915]

Enhancement

please add columne TABLE_NAME in table CHECK_CONSTRAINTS, so that we can clearly know which table the check constraints belongs to.

mysql> select version()\G;
*************************** 1. row ***************************
version(): 8.0.11-TiDB-v7.5.0-alpha
1 row in set (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
CONSTRAINT `check_1` CHECK ((1 < `a`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
CONSTRAINT `t_chk_1` CHECK ((1 < `a`)),
CONSTRAINT `t_chk_2` CHECK ((1 < `a`)),
CONSTRAINT `t_chk_3` CHECK ((1 < `a`)),
CONSTRAINT `check_1` CHECK ((1 < `a`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

mysql> select * from information_schema.CHECK_CONSTRAINTS;
+--------------------+-------------------+-----------------+--------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE |
+--------------------+-------------------+-----------------+--------------+
| def                | sbtest            | t_chk_1         | (1 < `a`)    |
| def                | sbtest            | t_chk_2         | (1 < `a`)    |
| def                | sbtest            | t_chk_3         | (1 < `a`)    |
| def                | sbtest            | check_1         | (1 < `a`)    |
| def                | sbtest            | check_1         | (1 < `a`)    |
+--------------------+-------------------+-----------------+--------------+
5 rows in set (0.00 sec)

reference:
in mariadb, that's more clearly.

MariaDB [sbtest]> select version();
+-------------------------------------+
| version()                           |
+-------------------------------------+
| 10.7.3-MariaDB-1:10.7.3+maria~focal |
+-------------------------------------+
1 row in set (0.000 sec)

MariaDB [sbtest]> select * from information_schema.CHECK_CONSTRAINTS;
+--------------------+-------------------+-------------------+-----------------+--------+----------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | TABLE_NAME        | CONSTRAINT_NAME | LEVEL  | CHECK_CLAUSE               |
+--------------------+-------------------+-------------------+-----------------+--------+----------------------------+
| def                | mysql             | global_priv       | Priv            | Column | json_valid(`Priv`)         |
| def                | sbtest            | t                 | CONSTRAINT_1    | Table  | 1 < `a`                    |
| def                | sbtest            | t                 | CONSTRAINT_2    | Table  | 1 < `a`                    |
| def                | sbtest            | t                 | CONSTRAINT_3    | Table  | 1 < `a`                    |
| def                | sbtest            | t                 | check_1         | Table  | 1 < `a`                    |
| def                | sbtest            | tt                | check_1         | Table  | 1 < `a`                    |

[xfworld]

Good Topic !!
Maintaining the basic raw data of the data is required to support the functionality in this direction

[shawn0915]

related pr: #46428

[shawn0915]

related topic: https://asktug.com/t/topic/1014356

[shawn0915]

@dveeden when you have time, could you help take a look?

[dveeden]

This is what MySQL 8.2.0 has:

sql> EXPLAIN information_schema.CHECK_CONSTRAINTS;
+--------------------+-------------+------+-----+---------+-------+
| 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    |       |
+--------------------+-------------+------+-----+---------+-------+
4 rows in set (0.0031 sec)

This is what it looks like in TiDB:

sql> EXPLAIN information_schema.CHECK_CONSTRAINTS;
+--------------------+-------------+------+-----+---------+-------+
| 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    |       |
+--------------------+-------------+------+-----+---------+-------+
4 rows in set (0.0009 sec)

This is what it looks like in PostgreSQL 15.3

postgres=# \d information_schema.CHECK_CONSTRAINTS
                       View "information_schema.check_constraints"
       Column       |               Type                | Collation | Nullable | Default 
--------------------+-----------------------------------+-----------+----------+---------
 constraint_catalog | information_schema.sql_identifier |           |          | 
 constraint_schema  | information_schema.sql_identifier |           |          | 
 constraint_name    | information_schema.sql_identifier |           |          | 
 check_clause       | information_schema.character_data |           |          | 

Also other implementations are using a similar structure:

• https://docs.databricks.com/en/sql/language-manual/information-schema/check_constraints.html
• https://learn.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/check-constraints-transact-sql
• https://www.ibm.com/docs/en/i/7.5?topic=views-check-constraints

With MySQL compatibility and ISO/IEC 9075-11 compliance in mind adding columns would create problems.

What we could do is to create another table (e.g. information_schema.TIDB_CHECK_CONSTRAINTS or similar that has extra columns.

[dveeden]

This would be similar to PostgreSQL where information_schema.CHECK_CONSTRAINTS is the standards conforming, portable version and pg_constraint is the PostgreSQL specific version.

postgres=# CREATE TABLE t1 (id INT PRIMARY KEY, CONSTRAINT c1 CHECK (id<50));
CREATE TABLE
postgres=# SELECT * FROM information_schema.CHECK_CONSTRAINTS WHERE constraint_name='c1';
-[ RECORD 1 ]------+------------
constraint_catalog | postgres
constraint_schema  | public
constraint_name    | c1
check_clause       | ((id < 50))

postgres=# SELECT * FROM pg_constraint WHERE conname='c1';
-[ RECORD 1 ]--+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid            | 17334
conname        | c1
connamespace   | 2200
contype        | c
condeferrable  | f
condeferred    | f
convalidated   | t
conrelid       | 17331
contypid       | 0
conindid       | 0
conparentid    | 0
confrelid      | 0
confupdtype    |  
confdeltype    |  
confmatchtype  |  
conislocal     | t
coninhcount    | 0
connoinherit   | f
conkey         | {1}
confkey        | 
conpfeqop      | 
conppeqop      | 
conffeqop      | 
confdelsetcols | 
conexclop      | 
conbin         | {OPEXPR :opno 97 :opfuncid 66 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 1 :varattnosyn 1 :location 58} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 61 :constvalue 4 [ 50 0 0 0 0 0 0 0 ]}) :location 60}

[shawn0915]

@dveeden Thank you very much for your reply.
if we can add table like tidb_constraint, that's can be acceptable, I think.
but, there's a easy way, that can be add a column named like tidb_schema_table.
since we have implemented in other tables.

https://docs.pingcap.com/tidb/dev/information-schema-partitions

| TIDB_PARTITION_ID             | bigint(21)   | YES  |      | NULL    |       |
| TIDB_PLACEMENT_POLICY_NAME    | varchar(64)  | YES  |      | NULL    |       |

https://docs.pingcap.com/tidb/dev/information-schema-tables

| TIDB_TABLE_ID             | bigint(21)    | YES  |      | NULL     |       |
| TIDB_ROW_ID_SHARDING_INFO | varchar(255)  | YES  |      | NULL     |       |

[dveeden]

With #48313 :

sql> SET GLOBAL tidb_enable_check_constraint=ON;
Query OK, 0 rows affected (0.0029 sec)

sql> CREATE TABLE t1(id INT PRIMARY KEY, CONSTRAINT chk_id CHECK (id<10));
Query OK, 0 rows affected (0.0151 sec)

sql> TABLE information_schema.CHECK_CONSTRAINTS;
+--------------------+-------------------+-----------------+--------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE |
+--------------------+-------------------+-----------------+--------------+
| def                | test              | chk_id          | (`id` < 10)  |
+--------------------+-------------------+-----------------+--------------+
1 row in set (0.0005 sec)

sql> TABLE information_schema.TIDB_CHECK_CONSTRAINTS;
+--------------------+-------------------+-----------------+--------------+------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE | TABLE_NAME | TABLE_ID |
+--------------------+-------------------+-----------------+--------------+------------+----------+
| def                | test              | chk_id          | (`id` < 10)  | t1         |      105 |
+--------------------+-------------------+-----------------+--------------+------------+----------+
1 row in set (0.0005 sec)