TABLE_CONSTRAINTS

TABLE_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)

TABLE_CONSTRAINTS 表中列的含义如下：

  • CONSTRAINT_CATALOG：约束所属的目录的名称。这个值总是 def。
  • CONSTRAINT_SCHEMA：约束所属的数据库的名称。
  • CONSTRAINT_NAME：约束的名称。
  • TABLE_NAME：表的名称。
  • CONSTRAINT_TYPE：约束的类型。取值可以是 UNIQUE、PRIMARY KEY 或者 FOREIGN KEY。UNIQUE 和 PRIMARY KEY 信息与 SHOW INDEX 语句的执行结果类似。