Note
This view is available starting with V2.2.30.
Purpose
This view displays the constraint information of all tables owned by the current user.
Applicability
This view is available only in Oracle-compatible mode of OceanBase Database.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The owner of the constraint. |
| CONSTRAINT_NAME | VARCHAR2(128) | NO | The name of the constraint. |
| CONSTRAINT_TYPE | VARCHAR2(1) | NO | The type of the index. Currently, only the NORMAL type is supported. |
| TABLE_NAME | VARCHAR2(128) | NO | The name of the table to which the constraint applies. |
| SEARCH_CONDITION | VARCHAR2(4000) | NO | The text of the search condition for the check constraint. |
| R_OWNER | VARCHAR2(128) | NO | The owner of the referenced constraint. |
| R_CONSTRAINT_NAME | VARCHAR2(128) | NO | The name of the referenced constraint. |
| DELETE_RULE | VARCHAR2(9) | NO | The cascading delete rule for the foreign key constraint. |
| STATUS | VARCHAR2(8) | NO | Indicates whether the constraint is ENABLE or DISABLE. |
| DEFERRABLE | VARCHAR2(14) | NO | Indicates whether the constraint is DEFERRABLE or NOT DEFERRABLE. |
| DEFERRED | VARCHAR2(9) | NO | Indicates whether the constraint is DEFERRED or IMMEDIATE. |
| VALIDATED | VARCHAR2(13) | NO | Indicates whether the constraint is VALIDATED or NOT VALIDATED |
| GENERATED | VARCHAR2(14) | NO | Indicates whether the constraint name is a USER NAME or a GENERATED NAME. |
| BAD | VARCHAR2(3) | NO | This column is not supported. The value of this column is NULL by default. |
| RELY | VARCHAR2(4) | NO | Indicates whether the constraint is in RELY mode. |
| LAST_CHANGE | DATE | NO | The last time when the constraint was changed to ENABLE or DISABLE. |
| INDEX_OWNER | VARCHAR2(128) | NO | The owner of the index on the constraint. |
| INDEX_NAME | VARCHAR2(128) | NO | The name of the index on the constraint. |
| INVALID | VARCHAR2(7) | NO | Indicates whether the constraint is in INVALIDATE mode. |
| VIEW_RELATED | VARCHAR2(14) | NO | Indicates whether the constraint is created on a view. |
Sample query
Query the constraint information of all tables owned by the current user.
obclient [SYS]> SELECT * FROM SYS.USER_CONSTRAINTS\G
The query result is as follows:
*************************** 1. row ***************************
OWNER: SYS
CONSTRAINT_NAME: TBL2_OBUNIQUE_1722234886020510
CONSTRAINT_TYPE: U
TABLE_NAME: TBL2
SEARCH_CONDITION: NULL
R_OWNER: NULL
R_CONSTRAINT_NAME: NULL
DELETE_RULE: NULL
STATUS: ENABLED
DEFERRABLE: NOT DEFERRABLE
DEFERRED: IMMEDIATE
VALIDATED: VALIDATED
GENERATED: GENERATED NAME
BAD: NULL
RELY: NULL
LAST_CHANGE: NULL
INDEX_OWNER: SYS
INDEX_NAME: TBL2_OBUNIQUE_1722234886020510
INVALID: NULL
VIEW_RELATED: NULL
1 row in set