From: stanb@panix.com (Stan Brown)
Newsgroups: comp.databases.oracle.server
Subject: Re: Help (2nd request) with determining parent key foreign key relationship
Date: 8 Aug 2001 12:26:50 -0400
Organization: PANIX Public Access Internet and UNIX, NYC
Lines: 93
Message-ID: <9krp8a$jah$1@panix2.panix.com>
References: <9krakb$ota$1@panix1.panix.com> <nae2nt4apcv394kcvdrhbs38a4lsq9687i@4ax.com>
NNTP-Posting-Host: panix2.panix.com
X-Trace: news.panix.com 997288011 3228 166.84.1.2 (8 Aug 2001 16:26:51 GMT)
X-Complaints-To: abuse@panix.com
NNTP-Posting-Date: 8 Aug 2001 16:26:51 GMT
X-Newsreader: NN version 6.5.6 (NOV)


In <nae2nt4apcv394kcvdrhbs38a4lsq9687i@4ax.com> Chuck Hamilton <chuck_hamilton@yahoo.com> writes:

>On 8 Aug 2001 08:17:15 -0400, stanb@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?