Purpose
This view displays the constraint information of all tables in the current tenant.
Applicability
This view is available only in OceanBase Database 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. Only the NORMAL type is supported. |
| TABLE_NAME | VARCHAR2(128) | NO | The name of the table where the constraint is located. |
| SEARCH_CONDITION | VARCHAR2(4000) | NO | The text of the check constraint search condition. |
| 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 cascade delete rule of 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-defined name or a generated name. |
| BAD | VARCHAR2(3) | NO | This column is not supported. It 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 INVALID. |
| VIEW_RELATED | VARCHAR2(14) | NO | Indicates whether the constraint is created on a view. |
Sample query
Query the constraint information of all tables in the current tenant.
obclient [SYS]> SELECT * FROM SYS.DBA_CONSTRAINTS\G
The query result is as follows:
*************************** 1. row ***************************
OWNER: oceanbase
CONSTRAINT_NAME: idx_transfer_partition_key
CONSTRAINT_TYPE: U
TABLE_NAME: __all_transfer_partition_task
SEARCH_CONDITION: NULL
R_OWNER: NULL
R_CONSTRAINT_NAME: NULL
DELETE_RULE: NULL
STATUS: ENABLED
DEFERRABLE: NOT DEFERRABLE
DEFERRED: IMMEDIATE
VALIDATED: VALIDATED
GENERATED: NULL
BAD: NULL
RELY: NULL
LAST_CHANGE: NULL
INDEX_OWNER: oceanbase
INDEX_NAME: idx_transfer_partition_key
INVALID: NULL
VIEW_RELATED: NULL
*************************** 2. row ***************************
OWNER: oceanbase
CONSTRAINT_NAME: idx_task_key
CONSTRAINT_TYPE: U
TABLE_NAME: __all_ddl_task_status
SEARCH_CONDITION: NULL
R_OWNER: NULL
R_CONSTRAINT_NAME: NULL
DELETE_RULE: NULL
STATUS: ENABLED
DEFERRABLE: NOT DEFERRABLE
DEFERRED: IMMEDIATE
VALIDATED: VALIDATED
GENERATED: NULL
BAD: NULL
RELY: NULL
LAST_CHANGE: NULL
INDEX_OWNER: oceanbase
INDEX_NAME: idx_task_key
INVALID: NULL
VIEW_RELATED: NULL
*************************** 3. 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
3 rows in set
References
Query the constraint information of all tables owned by the current user: USER_CONSTRAINTS
Query the constraint information of all tables accessible to the current user: ALL_CONSTRAINTS
For more information about the constraint types, see the following topics: