KEY_COLUMN_USAGE

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

  1. USE INFORMATION_SCHEMA;
  2. DESC KEY_COLUMN_USAGE;

The output is as follows:

  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_catalog                 | String |      | NO   |         | FIELD         |
  8. | real_table_catalog            | String |      | NO   |         | FIELD         |
  9. | table_schema                  | String |      | NO   |         | FIELD         |
  10. | table_name                    | String |      | NO   |         | FIELD         |
  11. | column_name                   | String |      | NO   |         | FIELD         |
  12. | ordinal_position              | UInt32 |      | NO   |         | FIELD         |
  13. | position_in_unique_constraint | UInt32 |      | YES  |         | FIELD         |
  14. | referenced_table_schema       | String |      | YES  |         | FIELD         |
  15. | referenced_table_name         | String |      | YES  |         | FIELD         |
  16. | referenced_column_name        | String |      | YES  |         | FIELD         |
  17. +-------------------------------+--------+------+------+---------+---------------+
  1. SELECT * FROM key_column_usage WHERE table_schema='public' and table_name='monitor'\G
  1. *************************** 1. row ***************************
  2.            constraint_catalog: def
  3.             constraint_schema: public
  4.               constraint_name: TIME INDEX
  5.                 table_catalog: def
  6.            real_table_catalog: greptime
  7.                  table_schema: public
  8.                    table_name: monitor
  9.                   column_name: ts
  10.              ordinal_position: 1
  11. position_in_unique_constraint: NULL
  12.       referenced_table_schema: NULL
  13.         referenced_table_name: NULL
  14.        referenced_column_name: NULL
  15. *************************** 2. row ***************************
  16.            constraint_catalog: def
  17.             constraint_schema: public
  18.               constraint_name: PRIMARY
  19.                 table_catalog: def
  20.            real_table_catalog: greptime
  21.                  table_schema: public
  22.                    table_name: monitor
  23.                   column_name: host
  24.              ordinal_position: 1
  25. position_in_unique_constraint: NULL
  26.       referenced_table_schema: NULL
  27.         referenced_table_name: NULL
  28.        referenced_column_name: NULL
  29. 2 rows in set (0.02 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 database to which the constraint belongs.
  • constraint_name: The name of the constraint.
  • table_catalog: The name of the catalog to which the table with the constraint belongs. The value is always def.
  • real_table_catalog: The real name of the catalog to which the table with the constraint belongs. The value is always greptime.
  • table_schema: The name of the database to which the table belongs.
  • table_name: The name of the table with the constraint.
  • column_name: The name of the column with the constraint.
  • 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 GreptimeDB, the value of this column in all constraints is NULL, except for the foreign key constraint.
  • referenced_table_name: The name of the table referenced by the constraint. Currently in GreptimeDB, the value of this column in all constraints is NULL, 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 NULL, except for the foreign key constraint.