DBA_CONSTRAINTS lists all constraints defined on tables across the entire database.
Columns
| Column | Type | Description |
|---|---|---|
owner | TEXT | Username of the constraint owner. |
schema_name | TEXT | Name of the schema that contains the constraint. |
constraint_name | TEXT | Name of the constraint. |
constraint_type | TEXT | Type of the constraint. Valid values: C (check), F (foreign key), P (primary key), U (unique key), R (referential integrity), V (constraint on a view), O (read-only on a view). |
table_name | TEXT | Name of the table the constraint belongs to. |
search_condition | TEXT | Search condition for a check constraint. |
r_owner | TEXT | Owner of the table referenced by a referential constraint. |
r_constraint_name | TEXT | Name of the constraint definition on the referenced table. |
delete_rule | TEXT | Delete rule for a referential constraint. Valid values: C (cascade), R (restrict), N (no action). |
deferrable | BOOLEAN | Whether the constraint is deferrable. T = deferrable, F = not deferrable. |
deferred | BOOLEAN | Whether the constraint is currently deferred. T = deferred, F = not deferred. |
index_owner | TEXT | Username of the index owner. |
index_name | TEXT | Name of the index associated with the constraint. |
constraint_def | TEXT | Definition of the constraint. |
Example
The following query retrieves all constraints on the EMPLOYEES table, including their types and delete rules:
SELECT constraint_name, constraint_type, search_condition, delete_rule
FROM dba_constraints
WHERE table_name = 'EMPLOYEES';