Home » SQL & PL/SQL » SQL & PL/SQL » something about user_constraints and foreign key
something about user_constraints and foreign key [message #270818] Fri, 28 September 2007 01:34 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
i have a question

suppose I have table A..

is there a way to find out the list of all the tables which
refer to table A with the help of user_constraints?

Re: something about user_constraints and foreign key [message #270819 is a reply to message #270818] Fri, 28 September 2007 01:35 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
YES
Re: something about user_constraints and foreign key [message #270823 is a reply to message #270818] Fri, 28 September 2007 01:49 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
select table_name
from user_constraints where r_constraint_name='PK_A'

will do...right? where pk_A is the name of primary key for
table A
Re: something about user_constraints and foreign key [message #270839 is a reply to message #270823] Fri, 28 September 2007 02:36 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

user_constraints as the name suggests it will give you details of all the constraints.

you asked for
Quote:
is there a way to find out the list of all the tables which refer to table A


so search for user_dependencies or dbms_utility.get_dependency.


regards,
Re: something about user_constraints and foreign key [message #270851 is a reply to message #270823] Fri, 28 September 2007 03:11 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could do something like:
select c1.table_name       parent_table
      ,c1.constraint_name  primary_key
      ,c2.table_name       child_table
      ,c2.constraint_name  foreign_key
from   user_constraints c1 
      ,user_constraints c2
where  c1.constraint_name = c2.r_constraint_name
order by parent_table,child_table;
Previous Topic: Deletion of records taking too much time
Next Topic: sequence cretion
Goto Forum:
  


Current Time: Sun Dec 04 17:05:11 CST 2016

Total time taken to generate the page: 0.11919 seconds