Re: Help (2nd request) with determining parent key foreign key relationship

In <9ku10c$mdc$1_at_panix2.panix.com> stanb_at_panix.com (Stan Brown) writes:

>In <8daca8eb.0108081249.1c25ef_at_posting.google.com> j_p_x_at_hotmail.com (OK) writes:
 

>>Here is what you want:
>>________________________
 

>Fistr of all thanks very muchs for the expert help on this.
 

>Now that I am work, and have been able to test this, I see that it does
>_almost_ exactly what I want.
 

>It lets me specify the table that I want to find out about parent keys for.
>It then returns, along with some data that is not of interest to me (as far
>as I know), the following things that I do need, Name of the table
>containing the parent key, name of the collumn containg canidate parent
>keys in the parent key table.
 

>This is wonderful stuff. However, it would be better if I could specify
>both the table that I wish to check for foreign keys, and the column of
>that table that I am checking
 

>Also on my second example table:

>CREATE TABLE brkr_cc_xref (
>brkr VARCHAR(5) NOT NULL ,
>brkr_level NUMBER (2,0) NOT NULL ,
>c_center VARCHAR(10) NOT NULL ,
>pct NUMBER(3,0) NOT NULL,
>CONSTRAINT brkr_cc_xref_index
>PRIMARY KEY (brkr, brkr_level, c_center)
>USING INDEX TABLESPACE CONFIG_TS ,
>FOREIGN KEY (brkr, brkr_level) REFERENCES brkr,
>FOREIGN KEY (c_center) REFERENCES cost_center,
>CHECK (pct <= 100),
>CHECK (pct >= 0) )
 

>It returns no rows. I suspect this is beacause of the 2 column foreign key?
>Even given that, why does it not reutrn the c_center foreign key?

Well, just brand me as an idiot. The above is absolutely incorect! It does indeed return results for the second tabel. However, maybe not exactly what I need. In th filed labled "Column Name", which is the PK tables column name, it returns "BRKR," Which is the first of the 2 FK columns. How can i get it to return both.

If it helps, the schema that I am devleoping this script for has not Pk/Fk relationships with more than 2 columns as a part of them

Thanks.

>Also, how can I properly deal with the 2 column foreign keys?
 

>To refresh the original question, the ultimate purpose of this query is to
>be able to present a scrolling list of choices for columns that have
>foreign keys, thus constraining the operators to entering only values that
>_do_ exist already as parent keys.
 

>Thanks.

>>set verify off linesize 120
>>ACCEPT tab_n PROMPT 'Enter the table name: '
 

>>col "FK Name" form A15 word_wrapped
>>col "PK Name" form A15 word_wrapped
>>col "Table Name" format a25
>>col "PK Table Name" format a25
>>col "Column Name" format a25 word_wrapped
>>col "FK Status" format a9
 

>>select u.table_name "Table Name", u.constraint_name "FK Name",
>>u.columns "Column Name",
>>w.r_constraint_name "PK Name", x.table_name "PK Table Name", u.status
>>"FK Status"
>>from
>>(select a.table_name, a.constraint_name, a.status,
>> max(decode(position, 1,
>>substr(column_name,1,30),NULL)) ||
>> max(decode(position, 2,',
>>'||substr(column_name,1,30),NULL)) ||
>> max(decode(position, 3,',
>>'||substr(column_name,1,30),NULL)) ||
>> max(decode(position, 4,',
>>'||substr(column_name,1,30),NULL)) ||
>> max(decode(position, 5,',
>>'||substr(column_name,1,30),NULL)) ||
>> max(decode(position, 6,',
>>'||substr(column_name,1,30),NULL)) ||
>> max(decode(position, 7,',
>>'||substr(column_name,1,30),NULL)) ||
>> max(decode(position, 8,',
>>'||substr(column_name,1,30),NULL)) ||
>> max(decode(position, 9,',
>>'||substr(column_name,1,30),NULL)) ||
>> max(decode(position,10,',
>>'||substr(column_name,1,30),NULL)) ||
>> max(decode(position,11,',
>>'||substr(column_name,1,30),NULL)) ||
>> max(decode(position,12,',
>>'||substr(column_name,1,30),NULL)) ||
>> max(decode(position,13,',
>>'||substr(column_name,1,30),NULL)) ||
>> max(decode(position,14,',
>>'||substr(column_name,1,30),NULL)) ||
>> max(decode(position,15,',
>>'||substr(column_name,1,30),NULL)) ||
>> max(decode(position,16,',
>>'||substr(column_name,1,30),NULL)) columns
>>from user_cons_columns b, user_constraints a
>>where a.table_name = UPPER('&tab_n') and
>>a.constraint_name = b.constraint_name
>>and a.constraint_type = 'R'
>>group by a.table_name, a.constraint_name, a.status) u,
>>user_constraints w,
>>user_constraints x
>>where u.constraint_name=w.constraint_name and
>>w.r_constraint_name=x.constraint_name
>>/
>>______________________
  Received on Thu Aug 09 2001 - 08:08:31 CDT