Note
This view is available starting with V1.4.
Purpose
This view displays the constraint information of keys in the column.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| CONSTRAINT_CATALOG | varchar(3) | NO | Fixed value (def) |
| CONSTRAINT_SCHEMA | varchar(128) | NO | Database name |
| CONSTRAINT_NAME | varchar(7) | NO | The constraint name. It can be PRIMARY, a column name, or a foreign key name. |
| TABLE_CATALOG | varchar(3) | NO | Table directory |
| 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 position of the column in the primary key constraint (starting from 1). |
| POSITION_IN_UNIQUE_CONSTRAINT | null | NO | For unique and primary key constraints, it indicates the order of the current column in the constraint definition. For foreign key constraints, it indicates the order 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 keys in the tbl4 table.
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