Select to corelate Parent/Foreign Keys

From: Stan Brown <stanb_at_panix.com>
Date: 6 Aug 2001 15:49:40 -0400
Message-ID: <9kmsck$fe0$1@panix3.panix.com>

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 Mon Aug 06 2001 - 14:49:40 CDT