Search This Blog

Saturday, May 25, 2013

Find a constraint in ORACLE Database

I am working in Oracle11g.
How can I retrieve the constraints associated with tables in Oracle.

select * from user_constraints;
From 'user_constraints' table I got constraint type and name (ALL_CONSTRAINTS), But that is not enough for me. I need
* The primary key field name
* Foreign key field name,reference table name and referencing field name in reference table. 
There are two tables I need to retrieve the constraints and the constraint attribute.
These two tables are
user_constraints / dba_constraints
user_cons_columns / dba_cons_columns
The below query will retrieve the information that I need.

select   a.table_name
        , a.constraint_name
        , a.constraint_type
        , b.table_name
        , b.column_name
        , b.position
from     user_constraints a
        , user_cons_columns b
where    a.owner = b.owner
and      a.constraint_name = b.constraint_name
and      a.constraint_type = 'P'
union all
select   a.table_name
        , a.constraint_name
        , a.constraint_type
        , b.table_name
        , b.column_name
        , b.position
from     user_constraints a
        , user_cons_columns b
where    a.owner = b.owner
and      a.r_constraint_name = b.constraint_name
and      a.CONSTRAINT_TYPE = 'R'
order by 1, 2, 3, 4, 5;

No comments: