Re: How to figure out the relationships between tables by querying view USER_CONSTRAINTS ?

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<tki9s9kmgenv73_at_beta-news.demon.nl>...
> "Jennifer Chang" <jchang_at_uxb2b.com> wrote in message
> news:9ib7ts$9mp$1_at_news.seed.net.tw...
> > Hi,
> > I want to figure out the relationships between a couple tables. The
 only
> > thing I knew was to query USER_CONSTRAINTS,
> > but all I got were names of constraints, not the column names of primary
> > key/foreign key of a table.
> > Please help, thank you very much.
> >
> >
> > Jennifer Chang
> >
> >
>
> Column names are in user_cons_columns
> You can easily join user_constraints and user_cons_columns for the correct
> info.
>
> Hth,
>
> Sybrand Bakker, Senior Oracle DBA

Another consideration is that many db applications do not declare PK and FK relationships in the database. Besides what you find in dba or user_constraints you may need to monitor application SQL and look at the columns being used in the join conditions between tables. When you spot two columns being used to join table B to several other tables and table B has a unique index on these columns you have probably found a PK to FK relationship even if it is not declared via constraints. Oracle trace with tkprof and/or the v$ views like v$sql_text can be used to do this, if necessary.

• Mark D Powell --