Oracle-L: Help find pk dependencies 7.3.4 db

From: Lisa R. Clary <lisa_at_cog.ufl.edu>
Date: Tue, 01 Oct 2002 13:08:27 -0800
Message-ID: <F001.004DDD65.20021001130827@fatcity.com>

I am trying to find out for a given table the column names for the parent table to which the referential integrity is built upon. for example, table B has primary keys=id, date_exam that are a foreign keys to table a, which has variable name pt_id, date_start. This is the query to deliver the pieces of information, but as soon as I remove the comment line (as I only want one line per return), it becomes a run-away and chews up the temp space. I have looked at this for so long that I am probably missing the obvious. Any thoughts?

select o.constraint_name ownerconstraint, o.table_name ownertable,r1.position, r1.column_name, r.constraint_name, r2.position, r2.column_name
from all_constraints o,

(select constraint_name, column_name, position from all_cons_columns ) r1,

      all_constraints r,
      (select constraint_name, column_name, position from all_cons_columns)

 where o.constraint_name=r1.constraint_name and
       o.constraint_type='R' and
       o.r_constraint_name  = r.constraint_name and
       r.constraint_name = r2.constraint_name and
 ---      r1.position= r2.position and
       o.table_name='NEURO_ASSESSMENT'

order by o.constraint_name, o.table_name;

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Lisa R. Clary
INET: lisa_at_cog.ufl.edu

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Oct 01 2002 - 16:08:27 CDT