Re: SQL query for foreign keys?

In <9jk7eb$rok$1_at_panix3.panix.com> stanb_at_panix.com (Stan Brown) writes:

>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;

	This references things withing the table I am checking _for_, and does
	not reference meter_id from the foreiegn key table at all, just to
	clarify the problem.

>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 31 2001 - 08:47:14 CDT