Re: Getting info on constraint from data dict. tables

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Fri, 24 Sep 1999 10:42:43 +0200
Message-ID: <7sfdji$alf$1@oceanite.cybercable.fr>

Is this what you want?

v734>create table t1
  2 (id1 number, id2 number, val1 number, val2 number,   3 constraint pk_t1 primary key (id1,id2),   4 constraint fk_t1_t1 foreign key (val2, val1) references t1);

Table created.

v734>
v734>create table t2
  2 (id number, t1id1 number, t1id2 number, t2id number, val number,

  3   constraint pk_t2 primary key (id),
  4   constraint fk_t2_t1 foreign key (t1id1, t1id2) references t1,
  5   constraint fk_t2_t2 foreign key (t2id) references t2);

Table created.

v734>
v734>column c1 format a5 heading "Table"
v734>column c2 format a11 heading "Foreign Key"
v734>column c3 format a16 heading "Referenced table"
v734>column c4 format a12 heading "Table column"
v734>column c5 format a17 heading "Referenced column"
v734>break on c1 skip 1 on c2 on c3
v734>select cons1.table_name c1,
  2         cons1.constraint_name c2,
  3         cons2.table_name c3,
  4         col1.column_name c4,
  5         col2.column_name c5
  6  from user_cons_columns col2, user_cons_columns col1,
  7       user_constraints cons2, user_constraints cons1

  8 where col2.position = col1.position   9 and ( col2.owner= cons2.owner
 10 and col2.constraint_name = cons2.constraint_name )  11 and ( col1.owner = cons1.owner
 12 and col1.constraint_name = cons1.constraint_name )  13 and ( cons2.owner = cons1.r_owner  14 and cons2.constraint_name = cons1.r_constraint_name )  15 and cons1.constraint_type = 'R'
 16 and cons1.table_name in ('T1','T2')  17 order by cons1.table_name, cons1.constraint_name, col2.position  18 /

Table Foreign Key Referenced table Table column Referenced column

----- ----------- ---------------- ------------ -----------------
T1    FK_T1_T1    T1               VAL2         ID1
                                   VAL1         ID2

T2    FK_T2_T1    T1               T1ID1        ID1
                                   T1ID2        ID2
      FK_T2_T2    T2               T2ID         ID

5 rows selected.

angelabr_at_my-deja.com a écrit dans le message <7sdijb$blr$1_at_nnrp1.deja.com>...
>Hi all,
>
>My problem is that I'm trying to find out the exact column that another
>is related to via a foreign key constraint.
>
> Table A Table B
> ---------- ----------
> column U -------> # column V
> column X -------> # column Y
> # column W column Z
>
>For example, Column X in Table A is a reference to Column Y in Table B,
>the primary key of Table B includes the column Y and the column V. By
>using the DBA_CONS_COLUMNS and DBA_CONSTRAINTS tables I can find out
>the column X (in table A) references the primary key of Table B ... BUT
>I don't know how I can determine that column X is only related to
>column Y (and not column V)! Any ideas?
>
>Thanks in advance,
>Angela
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Fri Sep 24 1999 - 03:42:43 CDT