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

From: Stan Brown <stanb_at_panix.com>
Date: 9 Aug 2001 08:51:24 -0400
Message-ID: <9ku10c$mdc$1@panix2.panix.com>

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?

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 - 07:51:24 CDT