Note
This view is available starting with V1.4.
Purpose
This view displays the constraint information of keys in a column.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| CONSTRAINT_CATALOG | varchar(3) | NO | Fixed value (def). |
| CONSTRAINT_SCHEMA | varchar(128) | NO | The name of the database. |
| CONSTRAINT_NAME | varchar(7) | NO | The name of the constraint, which can be PRIMARY, the name of a column, or the name of a foreign key. |
| TABLE_CATALOG | varchar(3) | NO | The table catalog. |
| TABLE_SCHEMA | varchar(128) | NO | The name of the database. |
| TABLE_NAME | varchar(256) | NO | The name of the table. |
| COLUMN_NAME | varchar(128) | NO | The name of the column. |
| ORDINAL_POSITION | bigint(20) | NO | The order of the column in the primary key constraint, starting from 1. |
| POSITION_IN_UNIQUE_CONSTRAINT | null | NO | For unique and primary key constraints, this column indicates the order of the current column in the constraint definition. For foreign key constraints, this column indicates the order 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