Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: creating a foreign key referencing a non-primary key

Re: creating a foreign key referencing a non-primary key

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 18 Mar 2004 10:02:00 +1100
Message-ID: <4058d8eb$0$20658$afc38c87@news.optusnet.com.au>

"Asya Kamsky" <asya_at_bayarea.net> wrote in message news:105hjmv8a7bn34f_at_corp.supernews.com...
> In article <4058c70c$0$8360$afc38c87_at_news.optusnet.com.au>,
> Howard J. Rogers <hjr_at_dizwell.com> wrote:
> >> Is there a way to create a foreign key that references a column
> >> that's not a primary key of the other table?
> >> (it happens to be part of a composite primary key).
> >> Example:
> >> t1 (id, version, primary_key (id, version))
> >> t2 (t1_id foreign_key on t1(id)) <- not allowed
> >Going on your own table definition, it would be possible to have ID 100
> >Version 1, and ID 100 Version 2. So when the child table displays ID 100,
> >which version number would you like Oracle to display when joined with
the
> >parent? Pick one at random, perhaps? It won't work, will it??!
>
> I want the "highest" or latest version.

Well, you might want that. But how's Oracle supposed to guess that that's what you want?

> Basically I want to assure that t2 t1_id exists in t1 and I want
> to do it without creating triggers on inserts and updates that
> check the t1 table. But it sounds like you're saying there is
> no other way to do it.

Well, there's always the possibility of correcting the design, of course. I mean, I can't tell exactly what is going on here in your mind, but you've got two completely seaparate entitites, IDs and Versions, but one table that's combined the two. You should have a separate table for IDs only; another separate table for Versions only. And a third table that pulls the two of them together to show what versions are valid for which IDs.

For example:

Table 1: Oracle 8i, Oracle 9i, Oracle 10g
Table 2: Release 1, Release 2, Release 3, Release 4
Table 3: Oracle 8i Release 1, Oracle 8i Release 2, Oracle 8i Release 3,
Oracle 9i Release 1, Oracle 9i Release 2, Oracle 10g Release 1.

Just my opinion, of course.

Regards
HJR Received on Wed Mar 17 2004 - 17:02:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US