KEY_COLUMN_USAGE

The KEY_COLUMN_USAGE table describes the key constraints of the columns, such as the primary key constraint.

  1. USE information_schema;
  2. DESC key_column_usage;
  1. +-------------------------------+--------------+------+------+---------+-------+
  2. | Field                         | Type         | Null | Key  | Default | Extra |
  3. +-------------------------------+--------------+------+------+---------+-------+
  4. | CONSTRAINT_CATALOG            | varchar(512) | NO   |      | NULL    |       |
  5. | CONSTRAINT_SCHEMA             | varchar(64)  | NO   |      | NULL    |       |
  6. | CONSTRAINT_NAME               | varchar(64)  | NO   |      | NULL    |       |
  7. | TABLE_CATALOG                 | varchar(512) | NO   |      | NULL    |       |
  8. | TABLE_SCHEMA                  | varchar(64)  | NO   |      | NULL    |       |
  9. | TABLE_NAME                    | varchar(64)  | NO   |      | NULL    |       |
  10. | COLUMN_NAME                   | varchar(64)  | NO   |      | NULL    |       |
  11. | ORDINAL_POSITION              | bigint(10)   | NO   |      | NULL    |       |
  12. | POSITION_IN_UNIQUE_CONSTRAINT | bigint(10)   | YES  |      | NULL    |       |
  13. | REFERENCED_TABLE_SCHEMA       | varchar(64)  | YES  |      | NULL    |       |
  14. | REFERENCED_TABLE_NAME         | varchar(64)  | YES  |      | NULL    |       |
  15. | REFERENCED_COLUMN_NAME        | varchar(64)  | YES  |      | NULL    |       |
  16. +-------------------------------+--------------+------+------+---------+-------+
  17. 12 rows in set (0.00 sec)
  1. SELECT * FROM key_column_usage WHERE table_schema='mysql' and table_name='user';
  1. *************************** 1. row ***************************
  2.            CONSTRAINT_CATALOG: def
  3.             CONSTRAINT_SCHEMA: mysql
  4.               CONSTRAINT_NAME: PRIMARY
  5.                 TABLE_CATALOG: def
  6.                  TABLE_SCHEMA: mysql
  7.                    TABLE_NAME: user
  8.                   COLUMN_NAME: Host
  9.              ORDINAL_POSITION: 1
  10. POSITION_IN_UNIQUE_CONSTRAINT: NULL
  11.       REFERENCED_TABLE_SCHEMA: NULL
  12.         REFERENCED_TABLE_NAME: NULL
  13.        REFERENCED_COLUMN_NAME: NULL
  14. *************************** 2. row ***************************
  15.            CONSTRAINT_CATALOG: def
  16.             CONSTRAINT_SCHEMA: mysql
  17.               CONSTRAINT_NAME: PRIMARY
  18.                 TABLE_CATALOG: def
  19.                  TABLE_SCHEMA: mysql
  20.                    TABLE_NAME: user
  21.                   COLUMN_NAME: User
  22.              ORDINAL_POSITION: 2
  23. POSITION_IN_UNIQUE_CONSTRAINT: NULL
  24.       REFERENCED_TABLE_SCHEMA: NULL
  25.         REFERENCED_TABLE_NAME: NULL
  26.        REFERENCED_COLUMN_NAME: NULL
  27. 2 rows in set (0.00 sec)

The description of columns in the KEY_COLUMN_USAGE table is as follows:

  • CONSTRAINT_CATALOG: The name of the catalog to which the constraint belongs. The value is always def.
  • CONSTRAINT_SCHEMA: The name of the schema to which the constraint belongs.
  • CONSTRAINT_NAME: The name of the constraint.
  • TABLE_CATALOG: The name of the catalog to which the table belongs. The value is always def.
  • TABLE_SCHEMA: The name of the schema to which the table belongs.
  • TABLE_NAME: The name of the table with constraints.
  • COLUMN_NAME: The name of the column with constraints.
  • ORDINAL_POSITION: The position of the column in the constraint, rather than in the table. The position number starts from 1.
  • POSITION_IN_UNIQUE_CONSTRAINT: The unique constraint and the primary key constraint are empty. For foreign key constraints, this column is the position of the referenced table’s key.
  • REFERENCED_TABLE_SCHEMA: The name of the schema referenced by the constraint. Currently in TiDB, the value of this column in all constraints is nil, except for the foreign key constraint.
  • REFERENCED_TABLE_NAME: The name of the table referenced by the constraint. Currently in TiDB, the value of this column in all constraints is nil, except for the foreign key constraint.
  • REFERENCED_COLUMN_NAME: The name of the column referenced by the constraint. Currently in TiDB, the value of this column in all constraints is nil, except for the foreign key constraint.