Skip to main content
Version: Nightly

TABLE_CONSTRAINTS

The TABLE_CONSTRAINTS table describes which tables have constraints.

DESC INFORMATION_SCHEMA.table_constraints;
+--------------------+--------+------+------+---------+---------------+
| Column             | Type   | Key  | Null | Default | Semantic Type |
+--------------------+--------+------+------+---------+---------------+
| constraint_catalog | String |      | NO   |         | FIELD         |
| constraint_schema  | String |      | NO   |         | FIELD         |
| constraint_name    | String |      | NO   |         | FIELD         |
| table_schema       | String |      | NO   |         | FIELD         |
| table_name         | String |      | NO   |         | FIELD         |
| constraint_type    | String |      | NO   |         | FIELD         |
| enforced           | String |      | NO   |         | FIELD         |
+--------------------+--------+------+------+---------+---------------+

The columns in the table:

  • 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, TIME INDEX or PRIMARY.
  • TABLE_NAME: The name of the table.
  • CONSTRAINT_TYPE: The type of the constraint. The value can be TIME INDEX or PRIMARY KEY. These key constraints are also visible in STATISTICS, which backs SHOW INDEX.
  • enforced: Doesn't support CHECK constraints, the value is always YES.
select * from INFORMATION_SCHEMA.table_constraints WHERE table_name = 'monitor'\G;

The output:

*************************** 1. row ***************************
constraint_catalog: def
 constraint_schema: public
   constraint_name: TIME INDEX
      table_schema: public
        table_name: monitor
   constraint_type: TIME INDEX
          enforced: YES
*************************** 2. row ***************************
constraint_catalog: def
 constraint_schema: public
   constraint_name: PRIMARY
      table_schema: public
        table_name: monitor
   constraint_type: PRIMARY KEY
          enforced: YES