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

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.

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.

--
Asya Kamsky

Come see my band, Monday March 29th, 11pm(sharp) @ Tongue and Groove in SF
http://www.evite.com/asya@bayarea.net/rock