LLI Wiki — My Digital Knowledge Base

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;
Mastodon LLIMastodon SocialFosstodon