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

From: Stan Brown <stanb_at_panix.com>
Date: 8 Aug 2001 12:26:50 -0400
Message-ID: <9krp8a$jah$1@panix2.panix.com>

In <nae2nt4apcv394kcvdrhbs38a4lsq9687i_at_4ax.com> Chuck Hamilton <chuck_hamilton_at_yahoo.com> writes:

>On 8 Aug 2001 08:17:15 -0400, stanb_at_panix.com (Stan Brown) wrote:
 

>>I posted this to comp.oraclemisc several days ago, and have recieved no
>>replies. I could really use a hand here, if someone can.
>>
>>Thanks,
>>
 

>What is it you're trying to do?

Sorry. This was suposed to be included:

I'm building a generic PerlTK script to allow some operations people to browse/edit any arbitrary table. The intent is to make it very easy touse/hard to make a mistake.

With this in mind, I would like to utodetec Foreign Key -> Parent Key relationships, and provide a scrolled list of avaialble choices if they are editing the tabel the contains foreign keys. I may later allow them to request editing the parent key table, if the choice is not there, but that's for later.

So Here is where I am. Here are a couple of typical tabel def's for foreign keys for us:

REATE TABLE brkr (

brkr         VARCHAR(5)       NOT NULL , 
brkr_level   NUMBER (2,0)     NOT NULL , 
descript     VARCHAR(80)      NOT NULL , 
gen          NUMBER(1,0)      NOT NULL , 
bought       NUMBER(1,0)      NOT NULL , 
consume      NUMBER(1,0)      NOT NULL ,  
meter_id     NUMBER (2,0),
CONSTRAINT  brkr_index            

PRIMARY KEY (brkr, brkr_level)
  USING INDEX TABLESPACE CONFIG_TS ,
  FOREIGN KEY (meter_id)

        REFERENCES meter,

  CHECK (gen IN(0,1)),
  CHECK (bought IN(0,1)),
  CHECK (consume IN(0,1))

  )

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

Hers is the part of this that I _do_ have working, thnaks to some very helpful people from this group:

SELECT
'select ' || cl.column_name || ' from ' || pk.table_name  FROM user_cons_columns cl,
  ( SELECT DISTINCT m.constraint_name,

				   m.table_name
				   FROM user_constraints  m,     -- table with PK
				   user_constraints  dt     -- table with FK
				   WHERE
				   dt.table_name  = \'$ltable\'
				   AND
				   m.constraint_name = dt.r_constraint_name ) pk
				   WHERE cl.constraint_name = pk.constraint_name

Returns a slect statement that when executed returns: the values for the Parent Keys, at least for the first example which has a simple 1 to 1 Fk -> Pk relation ship.

Problem one, how can I determine the name of the coulmn in the table that has the Fk? I need this to know which entry filed(s) get replaced with a scroling list.

Problem 2, How do I corealate the Pk's for the second example table, which has multiple Fk => Pk relationships?

I think I'm close here, but I'm missing the final peices of this puzzle.

Sugestiosn?                                                                                                                                          Received on Wed Aug 08 2001 - 11:26:50 CDT