Purpose
The ALL_CONSTRAINTS view displays the constraints on all tables accessible to the current user.
Applicability
This view is available only in OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL 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 constraint. At present, only the NORMAL type is supported. |
| TABLE_NAME | VARCHAR2(128) | NO | The name of the table containing the constraint. |
| SEARCH_CONDITION | VARCHAR2(4000) | NO | The search condition text 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 deletion rule for foreign key constraints. |
| STATUS | VARCHAR2(8) | NO | Indicates whether the constraint is enabled or disabled. Valid values are ENABLE and DISABLE. |
| DEFERRABLE | VARCHAR2(14) | NO | Indicates whether the constraint is deferrable. |
| DEFERRED | VARCHAR2(9) | NO | Indicates whether the constraint is deferred or immediate. |
| VALIDATED | VARCHAR2(13) | NO | Indicates whether the constraint is validated. |
| GENERATED | VARCHAR2(14) | NO | Indicates whether the name of the constraint is user-generated or system-generated. |
| BAD | VARCHAR2(3) | NO | At present, this column is not supported and its value is NULL by default. |
| RELY | VARCHAR2(4) | NO | Indicates whether the constraint is in the RELY state. |
| 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 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 constraints on all tables accessible to the current user.
obclient [SYS]> SELECT * FROM SYS.ALL_CONSTRAINTS\G
The query result is as follows:
*************************** 1. 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
*************************** 2. 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 rows in set