I am working in Oracle11g.
How can I retrieve the constraints associated with tables in Oracle.
* 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
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_constraintsThe below query will retrieve the information that I need.
user_cons_columns / dba_cons_columns
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