Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL query for foreign keys?

Re: SQL query for foreign keys?

From: Daniel A. Morgan <Daniel.Morgan_at_attws.com>
Date: Tue, 31 Jul 2001 11:13:45 -0700
Message-ID: <3B66F559.991E5DA1@attws.com>

Stan Brown wrote:

> In <9ji6v7$j2m$1_at_panix2.panix.com> stanb_at_panix.com (Stan Brown) writes:
>
> >Given a table name column name pair, what query can I run against the
> >system tables to find out:
 

> >1. If it has a foreign key.
> >2. If so, what the table name column name pair that it's key resieds in?
>
> Hi, I had to make a little trip for the past week, or so, and ho usenet
> access during this time.
>
> I am still unable to get this working. Could anyone provide further insigth
> as to how to acomplish this?
>
> What I am trying to do is determinet where to allow free form input, and
> where to force the user to select from a list of existing choices here.

Here's a view I built years ago that did it for a schema named 'OMS2':

SELECT T.owner AS CHILD_OWNER,

               T.table_name AS CHILD_TABLE,
               T.constraint_name AS FOREIGN_KEY_NAME,
               R.owner AS PARENT_OWNER,
               R.table_name AS PARENT_TABLE,
               R.constraint_name AS PARENT_CONSTRAINT
FROM dba_constraints T, dba_constraints R
WHERE T.r_constraint_name = R.constraint_name
  AND T.r_owner = R.owner
  AND T.constraint_type='R'
  AND R.owner = 'OMS2'
  AND T.owner = 'OMS2';

With a small amount of modification I've no doubt you can make this work.

Daniel A. Morgan Received on Tue Jul 31 2001 - 13:13:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US