Saturday, 25 May 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:

Post a Comment