Note
This view is available starting with V2.2.30.
Purpose
The view displays information about all constraints on tables owned by the user.
Applicability
This view applies only to OceanBase Database in Oracle mode.
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 that the constraint belongs to. |
| SEARCH_CONDITION | VARCHAR2(4000) | NO | The text of the search condition for a 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 a foreign key constraint. |
| STATUS | VARCHAR2(8) | NO | Whether the constraint is ENABLED or DISABLED. |
| DEFERRABLE | VARCHAR2(14) | NO | Whether the constraint is DEFERRABLE or NOT DEFERRABLE. |
| DEFERRED | VARCHAR2(9) | NO | Whether the constraint is DEFERRED or IMMEDIATE. |
| VALIDATED | VARCHAR2(13) | NO | Whether the constraint is VALIDATED or NOT VALIDATED. |
| GENERATED | VARCHAR2(14) | NO | Whether the name of the constraint is USER NAME or GENERATED NAME. |
| BAD | VARCHAR2(3) | NO | Not supported. The default value is NULL. |
| RELY | VARCHAR2(4) | NO | Whether the constraint is in RELY status. |
| LAST_CHANGE | DATE | NO | The time when the constraint was last enabled or disabled. |
| INDEX_OWNER | VARCHAR2(128) | NO | The owner of the index on which the constraint is defined. |
| INDEX_NAME | VARCHAR2(128) | NO | The name of the index on which the constraint is defined. |
| INVALID | VARCHAR2(7) | NO | Whether the constraint is in INVALIDATE status. |
| VIEW_RELATED | VARCHAR2(14) | NO | Whether the constraint is created on a view. |
Sample query
The following example queries information about all constraints on tables owned by the 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