Re: How do you find the original table and column a foreign key refering to?

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Tue, 22 Feb 2000 16:31:09 +0100
Message-ID: <88ua3s$282r$1_at_news5.isdnet.net>

No, there is no other way.
Follows a query that sums up what you're saying and gives all the foreign keys of a table with their columns and the primary keys associated.

col fk format a20
col fkcol format a20
col reftab format a20
col pk format a20
col pkcol format a20

break on fk skip 1 on reftab on pk

select cons.constraint_name fk,

       col.column_name fkcol,
       cons2.table_name reftab,
       cons2.constraint_name pk,
       col2.column_name pkcol
from user_cons_columns col2, user_cons_columns col,
     user_constraints cons2, user_constraints cons
where col2.owner = cons.r_owner

  and col2.constraint_name = cons.r_constraint_name   and col2.position = col.position
  and cons2.owner = cons.r_owner
  and cons2.constraint_name = cons.r_constraint_name   and col.owner = cons.owner
  and col.constraint_name = cons.constraint_name

  and cons.owner = user
  and cons.table_name = '<your table>'
  and cons.constraint_type = 'R'

/

--
Have a nice day
Michel


sc <cfs3526_at_hotmail.com> a écrit dans le message :
951227676.481092_at_newsreader-hpw1.net.bms.com...


> I use


>

> Select R_CONSTRAINT_NAME from user_constraints

> Where table_name='XXX' and constraint_type='F';

>

> After I got the R_CONSTRAINT_NAME, I use it to search against the

> user_constraints, like

>

> Select TABLE_NAME from user_constraints

> where constraint_name='R_CONSTRAINT_NAME';

>

> and then I need to search against the user_cons_columns to get the refered

> column name,

>

> Is this the only way to go, or there is more efficient way to do this?

>

> Thank you for your help.

>

>

Received on Tue Feb 22 2000 - 16:31:09 CET