OraFAQ Forum: SQL & PL/SQL » Foreign Key view in Oracle

Home » SQL & PL/SQL » SQL & PL/SQL » Foreign Key view in Oracle

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Foreign Key view in Oracle [message #522106]

Tue, 06 September 2011 09:14

gp185007
Messages: 45
Registered: April 2005
Location: Mumbai

Member

Hi All,

Is there any view in oracle which gives the foreign key mapping.

E.g.

Table DEPT has DEPTNO as PK.
Table Emp has DEPTNO as FK to DEPT.DEPTNO.

I can get the information for DEPT table from R_CONSTRAINT_NAME column of all_constraints table.

My requirement is to get the the which column of Emp table refers to DEPT.DEPTNO column .

Regards,
Ganesh

Report message to a moderator

Re: Foreign Key view in Oracle [message #522107 is a reply to message #522106]

Tue, 06 September 2011 09:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> desc dba_cons_columns
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER					   NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME			   NOT NULL VARCHAR2(30)
 TABLE_NAME				   NOT NULL VARCHAR2(30)
 COLUMN_NAME					    VARCHAR2(4000)
 POSITION					    NUMBER

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Tue, 06 September 2011 09:19]

Report message to a moderator

Re: Foreign Key view in Oracle [message #522108 is a reply to message #522106]

Tue, 06 September 2011 09:18

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

USER/ALL/DBA_CONS_COLUMNS should help here.

Report message to a moderator

Re: Foreign Key view in Oracle [message #522110 is a reply to message #522106]

Tue, 06 September 2011 10:15

Michel Cadot
Messages: 68764
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

All these questions can be answered either refering to Database Reference which contains the description of all catalog views and it is easy to find CONSTRAINT in its toc; either using DICT view in the following way:

select * from dict where lower(comments) like '%constraint%';

Hope you will not have to repost such question now.

Regards
Michel

Report message to a moderator

Previous Topic:	update statement
Next Topic:	Suppressing ORA-06512 & ORA-04088 when using RAISE_APPLICATION_ERROR?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Aug 02 23:35:31 CDT 2025