Re: Question about foreign keys
Date: 1996/05/27
Message-ID: <4oc94r$1tta_at_usenetp1.news.prodigy.com>#1/1
> I have a foreign key and need to find the table/column
>it is referencing, I've tried DBA_CONS_COLUMNS and DBA_CONSTRAINTS
>and could not get the clue, please help.
You are on the right track. DBA_CONSTRAINTS will have an entry for your
foreign key constraint - look it up by owner and constraint_name (what?,
you don't name your constraints? then list all the constraints for the
table where constraint_type = 'R', and figure out which one is the one
you are interested in). The entry in DBA_CONSTRAINTS will have the name
of the referenced table and its owner. Now, do another query in
DBA_CONSTRAINTS, where the owner and table match the referenced owner and
table and the constraint_type='P' - this is the Primary Key constraint of
the referenced table. Finally, query DBA_CONS_COLUMNS where the
constraint_name and owner = the name of the referenced table's Primary
Key constraint. This will get you the columns referenced.
I know that there has got to be a way to do a join that gets all of this information in one query, but I haven't figured one out yet. Received on Mon May 27 1996 - 00:00:00 CEST