Re: Foreign keys data dictionary

From: <aacesta2_at_my-dejanews.com>
Date: Tue, 15 Dec 1998 11:35:23 GMT
Message-ID: <755hhr$v97$1@nnrp1.dejanews.com>

No I don't like talking to myself, but I've found myself some way to do that and wanna share with the ones interested in the same problem:

After a little talk with some friends I found out that this select on the data dictionary returns the desired information, altough I couldn't optimize the select, because I don't know the indexes of the data dictionary:

SELECT
    fk.constraint_name,
    fk.table_name,
    cfk.column_name,
    rk.table_name,
    crk.column_name,
    crk.position
FROM

    user_cons_columns crk,
    user_constraints  rk,
    user_cons_columns cfk,
    user_constraints  fk

WHERE

    fk.constraint_type = 'R' and
    fk.table_name like '%SN%' and
    fk.constraint_name = cfk.constraint_name and
    fk.r_constraint_name = rk.constraint_name and
    rk.constraint_name = crk.constraint_name and

    crk.position = cfk.position
ORDER BY 1,2,6; In article <752qfu$ku6$1_at_nnrp1.dejanews.com>,   aacesta2_at_my-dejanews.com wrote:
> Hello,
>
> I couldn't reconstruct a foreign key information through the data
> dictionary. I can reconstruct a primary key information, but not foreign key.
>
> I want to be able to do a select in the data dictionary that returns
> information equivalente to the foreign key creation sql:
>
> ALTER TABLE SN_REL_CONTRATO_PRODUTO ADD (
> CONSTRAINT FKSN_REL_CONTRATO_PRODUTO
> FOREIGN KEY (NUM_CONTRATO,
> CID_CONTRATO)
> REFERENCES SN_CONTRATO (
> NUM_CONTRATO,
> CID_CONTRATO)
> )
>
> The information from the oracle data dictionary could be returned like this:
> FN_NAME TABLE_NAME COLUMN REFERENCED_TABLE_NAME REFERENCED_TABLE_COLUMN
>
> Thanks for any help:
>
> Cesta
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Dec 15 1998 - 05:35:23 CST