Re: Oracle repository and constraints questions:

select c1.constraint_name, c2.table_name referred_table from all_constraints c1, all_constraints c2 where c1.owner='YOU'
and c1.table_name='JUNK'
and c1.r_constraint_name=c2.constraint_name

It will give you the table name the constraint referring to. Join all_cons_columns if you want to know the referred column too.

JQ

argosy22_at_my-deja.com wrote in message <7m2r1t$j9s$1_at_nnrp1.deja.com>...
>Hello,
>
>
>We are using Oracle 7.3.4.
>
>We can create a foreign key in a table
>with something like:
>
>create table junk
>(
>field1 number(10);
>field2 number(5)
> constraint foreign_key_constraint
> references othertable(otherfield)
>);
>
>
>The question is, what is the query to the
>Oracle repository to discover this relationship?
>I have been looking at the views:
>All_constraints, All_con_columns
>
>and I can find one table name.
>
>Select * From user_cons_columns
>
>OWNER CONSTRAINT_NAME TABLE_NAME
>------------------------------ ------------------------------
>-----------------
>COLUMN_NAME POSITION
>------------------------------ ---------
>*?****** CATEGORY_CAT_DETAIL_FK CATEGORY
>DET1 1
>
>*?****** CATEGORY_CAT_DETAIL_FK CATEGORY
>DET2 2
>
>
>But, how do I find the other table name?
>
>
>Thanks in advance,
>
>Rodger
>
>
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Thu Jul 08 1999 - 14:11:01 CDT