Re: SQL query for foreign keys?

From: Stan Brown <stanb_at_panix.com>
Date: 24 Jul 2001 12:23:39 -0400
Message-ID: <9jk7eb$rok$1@panix3.panix.com>

In <9jk20e$n4u$1_at_fermi.tro.net> "Kay Kanekowski" <kkanekowski_at_mindcommerce.de> writes:

>Hi,
 

>try this
 

>SELECT 'select ' || cl.column_name || ' from ' || pk.table_name || ';'
> FROM user_cons_columns cl,
> ( SELECT DISTINCT dt.constraint_name, m.table_name
> FROM user_constraints m, -- table with PK
> user_constraints dt -- table with FK
> WHERE m.table_name LIKE UPPER( '%' )
> AND m.constraint_name = dt.r_constraint_name ) pk
>WHERE cl.constraint_name = pk.constraint_name;
 

>the result will be something like this
 

>DYNAMIC_STATEMENT
>------------------------------------
>select METER_ID from METER;

>Put in script and run it or give the result to a varchar and then execute a dynamic sql.
>That works only with primary key that have only one column and you need a description column
>for your list_box i think.

Pardon me for being a complete idiot, but heres what I get when I run

'SELECT'||CL.COLUMN_NAME||'FROM'||PK.TABLE_NAME||';'

---

select BRKR from BRKR;
select BRKR_LEVEL from BRKR;
select SOURCE_BRKR from BRKR;
select SOURCE_BRKR_LEVEL from BRKR;
select CONSUMER_BRKR from BRKR;
select CONSUMER_BRKR_LEVEL from BRKR;

SELECT 'select ' || cl.column_name || ' from ' || pk.table_name || ';'   FROM user_cons_columns cl,

     ( SELECT DISTINCT dt.constraint_name, m.table_name
         FROM user_constraints  m,   -- table with PK
              user_constraints  dt     -- table with FK
        WHERE m.table_name      LIKE UPPER( 'BRKR )
          AND m.constraint_name = dt.r_constraint_name ) pk

WHERE cl.constraint_name = pk.constraint_name;

as you can see this does not retunr the correct data.

Don't I need to specify teh table name, and the column name that I am checking to see if it's a foreign key?

Thanks again, and I apolgize for not understnading where you are headed with this. Received on Tue Jul 24 2001 - 11:23:39 CDT