Note
This view is available starting with V2.2.77.
Purpose
This view displays metadata about foreign key constraints.
Columns
Column |
Type |
Nullable |
Description |
|---|---|---|---|
| CONSTRAINT_CATALOG | varchar(64) | NO | The value is always def. |
| CONSTRAINT_SCHEMA | varchar(128) | NO | The name of the database to which the constraint belongs. |
| CONSTRAINT_NAME | varchar(128) | NO | The name of the constraint. |
| UNIQUE_CONSTRAINT_CATALOG | varchar(64) | NO | The value is always def. |
| UNIQUE_CONSTRAINT_SCHEMA | varchar(128) | NO | The name of the database to which the unique constraint or primary key referenced by the constraint belongs. |
| UNIQUE_CONSTRAINT_NAME | varchar(128) | NO | The name of the unique constraint or primary key referenced by the constraint. |
| MATCH_OPTION | varchar(64) | NO | The value of the constraint MATCH option. The only valid value is NONE. |
| UPDATE_RULE | varchar(64) | NO | The value of the constraint ON UPDATE option. Valid values: CASCADE, SET NULL, RESTRICT, NO ACTION |
| DELETE_RULE | varchar(64) | NO | The value of the constraint ON DELETE option. Valid values: CASCADE, SET NULL, RESTRICT, NO ACTION |
| TABLE_NAME | varchar(256) | NO | The name of the child table to which the constraint belongs. |
| REFERENCED_TABLE_NAME | varchar(256) | NO | The name of the parent table referenced by the constraint. |
Sample query
Query the metadata about foreign key constraints in the tbl4 table.
obclient [oceanbase]> SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE TABLE_NAME='tbl4';
The query result is as follows:
+--------------------+-------------------+----------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME | REFERENCED_TABLE_NAME |
+--------------------+-------------------+----------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+
| def | infotest | tbl4_OBFK_1736909788672362 | def | infotest | PRIMARY | NONE | CASCADE | RESTRICT | tbl4 | tbl2 |
+--------------------+-------------------+----------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+
1 row in set