TABLE_CONSTRAINTS

The TABLE_CONSTRAINTS table describes which tables have constraints.

  1. USE information_schema;
  2. DESC table_constraints;
  1. +--------------------+--------------+------+------+---------+-------+
  2. | Field              | Type         | Null | Key  | Default | Extra |
  3. +--------------------+--------------+------+------+---------+-------+
  4. | CONSTRAINT_CATALOG | varchar(512) | YES  |      | NULL    |       |
  5. | CONSTRAINT_SCHEMA  | varchar(64)  | YES  |      | NULL    |       |
  6. | CONSTRAINT_NAME    | varchar(64)  | YES  |      | NULL    |       |
  7. | TABLE_SCHEMA       | varchar(64)  | YES  |      | NULL    |       |
  8. | TABLE_NAME         | varchar(64)  | YES  |      | NULL    |       |
  9. | CONSTRAINT_TYPE    | varchar(64)  | YES  |      | NULL    |       |
  10. +--------------------+--------------+------+------+---------+-------+
  11. 6 rows in set (0.00 sec)
  1. SELECT * FROM table_constraints WHERE constraint_type='UNIQUE';
  1. +--------------------+--------------------+-------------------------+--------------------+-------------------------------------+-----------------+
  2. | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA  | CONSTRAINT_NAME         | TABLE_SCHEMA       | TABLE_NAME                          | CONSTRAINT_TYPE |
  3. +--------------------+--------------------+-------------------------+--------------------+-------------------------------------+-----------------+
  4. | def                | mysql              | name                    | mysql              | help_topic                          | UNIQUE          |
  5. | def                | mysql              | tbl                     | mysql              | stats_meta                          | UNIQUE          |
  6. | def                | mysql              | tbl                     | mysql              | stats_histograms                    | UNIQUE          |
  7. | def                | mysql              | tbl                     | mysql              | stats_buckets                       | UNIQUE          |
  8. | def                | mysql              | delete_range_index      | mysql              | gc_delete_range                     | UNIQUE          |
  9. | def                | mysql              | delete_range_done_index | mysql              | gc_delete_range_done                | UNIQUE          |
  10. | def                | PERFORMANCE_SCHEMA | SCHEMA_NAME             | PERFORMANCE_SCHEMA | events_statements_summary_by_digest | UNIQUE          |
  11. +--------------------+--------------------+-------------------------+--------------------+-------------------------------------+-----------------+
  12. 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.