Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL query for foreign keys?
"Kay Kanekowski" <kkanekowski_at_mindcommerce.de> wrote in message
news:9jj4n4$gi9$1_at_fermi.tro.net...
> Hi ,
>
> try this
>
> SELECT *
> FROM user_cons_columns
> WHERE constraint_name IN
> ( SELECT DISTINCT dt.constraint_name
> FROM user_constraints m, -- table with PK
> user_constraints dt -- table with FK
> WHERE m.table_name = UPPER( '&1' )
> AND m.constraint_name = dt.r_constraint_name );
>
> hth
> Kay
>
> Mail: kkanekowski_at_mindcommerce.de
>
>
>
Just one small tip for you: the result of a subquery is a set. Sets are unique by design. Your distinct is redundant. Oracle however is stupid enough to perform 2 distincts on the results on the subquery.
Regards,
Sybrand Bakker, Senior Oracle DBA Received on Tue Jul 24 2001 - 13:35:26 CDT