Overview
Displays the constraint information of the key in the column.
Columns
Field |
Type |
Nullable |
Description |
|---|---|---|---|
| CONSTRAINT_CATALOG | varchar(3) | NO | Fixed value (def) |
| CONSTRAINT_SCHEMA | varchar(128) | NO | Database Name |
| CONSTRAINT_NAME | varchar(7) | NO | Constraint name, which can be PRIMARY KEY, column name, or foreign key name. |
| TABLE_CATALOG | varchar(3) | NO | Table of Contents |
| TABLE_SCHEMA | varchar(128) | NO | Database Name |
| TABLE_NAME | varchar(256) | NO | Table name |
| COLUMN_NAME | varchar(128) | NO | Column name |
| ORDINAL_POSITION | bigint(20) | NO | The order position of the columns in the primary key constraint (starting from 1). |
| POSITION_IN_UNIQUE_CONSTRAINT | null | NO | For unique and primary key constraints, it refers to the order of the current column in the constraint definition. For foreign key constraints, it refers to the position of the key in the referenced table. |
| REFERENCED_TABLE_SCHEMA | varchar(128) | NO | Default value: NULL |
| REFERENCED_TABLE_NAME | varchar(256) | NO | Default value: NULL |
| REFERENCED_COLUMN_NAME | varchar(128) | NO | Default value: NULL |
Sample query
Query the constraint information of the key in column tbl4.
obclient [oceanbase]> SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME='tbl4';
The query result is as follows:
+--------------------+-------------------+----------------------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------------+-------------------+----------------------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| def | infotest | tbl4_OBFK_1736909788672362 | def | infotest | tbl4 | col2 | 1 | 1 | infotest | tbl2 | col1 |
+--------------------+-------------------+----------------------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
1 row in set