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

"Asya Kamsky" <asya_at_bayarea.net> wrote in message news:105hh1q6ti25bfb_at_corp.supernews.com...
> 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
>
> --
> Asya Kamsky

No, is the short answer. Well, it's the long answer too, for obvious reasons if you think about it. By definition, a column that is merely part of a primary key cannot be guaranteed 100% unique (if it could, then you wouldn't need the composite primary key in the first place). And if you allowed a child table to reference an ambiguous key in the parent table, you would have an awful, logical, mess on your hands.

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??!

You can't reference a unique key, either, because although the ambiguity problem is dealt with by declaring the parent column unique, NULL entries are permitted to exist in the parent table's key column, and that would also cause massive problems when joining the child to the parent, because null never equals null (or anything else for that matter).

Regards
HJR Received on Wed Mar 17 2004 - 15:45:46 CST