TABLE_CONSTRAINTS

The TABLE_CONSTRAINTS table describes which tables have constraints.

  1. DESC INFORMATION_SCHEMA.table_constraints;
  1. +--------------------+--------+------+------+---------+---------------+
  2. | Column             | Type   | Key  | Null | Default | Semantic Type |
  3. +--------------------+--------+------+------+---------+---------------+
  4. | constraint_catalog | String |      | NO   |         | FIELD         |
  5. | constraint_schema  | String |      | NO   |         | FIELD         |
  6. | constraint_name    | String |      | NO   |         | FIELD         |
  7. | table_schema       | String |      | NO   |         | FIELD         |
  8. | table_name         | String |      | NO   |         | FIELD         |
  9. | constraint_type    | String |      | NO   |         | FIELD         |
  10. | enforced           | String |      | NO   |         | FIELD         |
  11. +--------------------+--------+------+------+---------+---------------+

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. The TIME INDEX and PRIMARY KEY information is similar to the execution result of the SHOW INDEX statement.
  • enforced: Doesn’t support CHECK constraints, the value is always YES.
  1. select * from INFORMATION_SCHEMA.table_constraints WHERE table_name = 'monitor'\G;

The output:

  1. *************************** 1. row ***************************
  2. constraint_catalog: def
  3.  constraint_schema: public
  4.    constraint_name: TIME INDEX
  5.       table_schema: public
  6.         table_name: monitor
  7.    constraint_type: TIME INDEX
  8.           enforced: YES
  9. *************************** 2. row ***************************
  10. constraint_catalog: def
  11.  constraint_schema: public
  12.    constraint_name: PRIMARY
  13.       table_schema: public
  14.         table_name: monitor
  15.    constraint_type: PRIMARY KEY
  16.           enforced: YES