Note
This view is available starting with V2.2.30.
Purpose
This view displays information about constraints on all tables in the database.
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 on which the constraint is defined. |
| SEARCH_CONDITION | VARCHAR2(4000) | NO | The search condition of 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 of the foreign key constraint. |
| STATUS | VARCHAR2(8) | NO | The ENABLE or DISABLE status of the constraint. |
| DEFERRABLE | VARCHAR2(14) | NO | The DEFERRABLE or NOT DEFERRABLE status of the constraint. |
| DEFERRED | VARCHAR2(9) | NO | The DEFERRED or IMMEDIATE status of the constraint. |
| VALIDATED | VARCHAR2(13) | NO | The VALIDATED or NOT VALIDATED status of the constraint. |
| GENERATED | VARCHAR2(14) | NO | The USER NAME or GENERATED NAME status of the constraint. |
| BAD | VARCHAR2(3) | NO | This column is not supported. The default value is NULL. |
| RELY | VARCHAR2(4) | NO | The RELY status of the constraint. |
| 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 | The INVALIDATE status of the constraint. |
| VIEW_RELATED | VARCHAR2(14) | NO | Indicates whether the constraint is defined on a view. |
Sample query
The following example queries information about constraints on all tables in the database.
obclient [SYS]> SELECT * FROM SYS.DBA_CONSTRAINTS\G
The sample 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