Important

You are viewing the documentation of TiDB v6.0 (DMR). PingCAP does not provide bug fixes for v6.0. Any bugs will be fixed in future releases.

For general purposes, please use the latest LTS version of the TiDB database.

KEY_COLUMN_USAGE

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

USE information_schema;
DESC key_column_usage;
+-------------------------------+--------------+------+------+---------+-------+
| Field                         | Type         | Null | Key  | Default | Extra |
+-------------------------------+--------------+------+------+---------+-------+
| CONSTRAINT_CATALOG            | varchar(512) | NO   |      | NULL    |       |
| CONSTRAINT_SCHEMA             | varchar(64)  | NO   |      | NULL    |       |
| CONSTRAINT_NAME               | varchar(64)  | NO   |      | NULL    |       |
| TABLE_CATALOG                 | varchar(512) | NO   |      | NULL    |       |
| TABLE_SCHEMA                  | varchar(64)  | NO   |      | NULL    |       |
| TABLE_NAME                    | varchar(64)  | NO   |      | NULL    |       |
| COLUMN_NAME                   | varchar(64)  | NO   |      | NULL    |       |
| ORDINAL_POSITION              | bigint(10)   | NO   |      | NULL    |       |
| POSITION_IN_UNIQUE_CONSTRAINT | bigint(10)   | YES  |      | NULL    |       |
| REFERENCED_TABLE_SCHEMA       | varchar(64)  | YES  |      | NULL    |       |
| REFERENCED_TABLE_NAME         | varchar(64)  | YES  |      | NULL    |       |
| REFERENCED_COLUMN_NAME        | varchar(64)  | YES  |      | NULL    |       |
+-------------------------------+--------------+------+------+---------+-------+
12 rows in set (0.00 sec)
SELECT * FROM key_column_usage WHERE table_schema='mysql' and table_name='user';
*************************** 1. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: mysql
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: mysql
                   TABLE_NAME: user
                  COLUMN_NAME: Host
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
*************************** 2. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: mysql
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: mysql
                   TABLE_NAME: user
                  COLUMN_NAME: User
             ORDINAL_POSITION: 2
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
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.
Was this page helpful?