Skip to content

Latest commit

 

History

History
58 lines (48 loc) · 3.25 KB

information-schema-table-constraints.md

File metadata and controls

58 lines (48 loc) · 3.25 KB
title summary
TABLE_CONSTRAINTS
Learn the `TABLE_CONSTRAINTS` information_schema table.

TABLE_CONSTRAINTS

The TABLE_CONSTRAINTS table describes which tables have constraints.

{{< copyable "sql" >}}

USE information_schema;
DESC table_constraints;
+--------------------+--------------+------+------+---------+-------+
| Field              | Type         | Null | Key  | Default | Extra |
+--------------------+--------------+------+------+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | YES  |      | NULL    |       |
| CONSTRAINT_SCHEMA  | varchar(64)  | YES  |      | NULL    |       |
| CONSTRAINT_NAME    | varchar(64)  | YES  |      | NULL    |       |
| TABLE_SCHEMA       | varchar(64)  | YES  |      | NULL    |       |
| TABLE_NAME         | varchar(64)  | YES  |      | NULL    |       |
| CONSTRAINT_TYPE    | varchar(64)  | YES  |      | NULL    |       |
+--------------------+--------------+------+------+---------+-------+
6 rows in set (0.00 sec)

{{< copyable "sql" >}}

SELECT * FROM table_constraints WHERE constraint_type='UNIQUE';
+--------------------+--------------------+-------------------------+--------------------+-------------------------------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA  | CONSTRAINT_NAME         | TABLE_SCHEMA       | TABLE_NAME                          | CONSTRAINT_TYPE |
+--------------------+--------------------+-------------------------+--------------------+-------------------------------------+-----------------+
| def                | mysql              | name                    | mysql              | help_topic                          | UNIQUE          |
| def                | mysql              | tbl                     | mysql              | stats_meta                          | UNIQUE          |
| def                | mysql              | tbl                     | mysql              | stats_histograms                    | UNIQUE          |
| def                | mysql              | tbl                     | mysql              | stats_buckets                       | UNIQUE          |
| def                | mysql              | delete_range_index      | mysql              | gc_delete_range                     | UNIQUE          |
| def                | mysql              | delete_range_done_index | mysql              | gc_delete_range_done                | UNIQUE          |
| def                | PERFORMANCE_SCHEMA | SCHEMA_NAME             | PERFORMANCE_SCHEMA | events_statements_summary_by_digest | UNIQUE          |
+--------------------+--------------------+-------------------------+--------------------+-------------------------------------+-----------------+
7 rows in set (0.01 sec)

Fields in the TABLE_CONSTRAINTS table are described as follows:

  • CONSTRAINT_CATALOG: The name of the catalog to which the constraint belongs. This value is always def.
  • CONSTRAINT_SCHEMA: The name of the database to which the constraint belongs.
  • CONSTRAINT_NAME: The name of the constraint.
  • TABLE_NAME: The name of the table.
  • CONSTRAINT_TYPE: The type of the constraint. The value can be UNIQUE, PRIMARY KEY or FOREIGN KEY. The UNIQUE and PRIMARY KEY information is similar to the execution result of the SHOW INDEX statement.