List Oracle constraints with DBA_CONSTRAINTS
Queries on DBA_CONSTRAINTS and DBA_CONS_COLUMNS to list constraints, their columns and references.
The DBA_CONSTRAINTS, USER_CONSTRAINTS and ALL_CONSTRAINTS views list constraints along with their type.
List the constraints of a table
select owner, constraint_name, constraint_type
from dba_constraints
where owner = '&Owner_name' and table_name = '&Tab_name';
The constraint_type column can contain the following values:
| Value | Meaning |
|---|---|
C |
check constraint on a table |
P |
primary key |
U |
unique key |
R |
referential integrity |
V |
WITH CHECK OPTION, on a view |
O |
WITH READ ONLY, on a view |
Constraint columns
The DBA_CONS_COLUMNS, USER_CONS_COLUMNS and ALL_CONS_COLUMNS views give the columns and their position:
col owner for a10
col constraint_name for a27
col table_name for a25
col column_name for a23
set pages 100
select * from dba_cons_columns
where table_name = '&Tab_name' and owner = '&Owner_name'
order by constraint_name, position;
Joining both views
By joining the two views you get the constraints, their type, column name and position, and the referenced constraint and table:
col r_owner for a10
select c.constraint_name, cc.column_name, c.r_owner, c.r_constraint_name,
c.constraint_type, cc.position, r.table_name
from dba_constraints c
JOIN dba_cons_columns cc
ON (c.table_name = cc.table_name AND c.owner = cc.owner
AND c.constraint_name = cc.constraint_name)
LEFT JOIN dba_constraints r
ON (c.r_constraint_name = r.constraint_name AND r.constraint_type in ('P','U'))
where c.table_name = '&Tab_name' and c.owner = '&Owner_name'
order by constraint_name, position;