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

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

>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

On second thought, after runing a few test, and seeing how long this query takes to process, forget that!. I'll be _lots_ better of just running this query once per table. So, having said thta, could I get it to also return the colimn name in the child (Foreign Key) table.

So, what is hould retun is something like this:

FK Column, PK Table, PK Column

>>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 - 09:02:06 CDT