"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<405a15da$0$31902$afc38c87_at_news.optusnet.com.au>...
> "Bob Jenkins" <bob_jenkins_at_burtleburtle.net> wrote in message
> news:a5d787df.0403180941.15303f98_at_posting.google.com...
> > asya_at_bayarea.net (Asya Kamsky) 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
> >
> > If you did
> > alter table t1 add constraint t1uk unique (id);
> > then you could do
> > create table t2 (t1_id references t1 (id));
> > You can indeed have foreign keys reference unique keys.
>
>
> You can indeed and I don't know what I was thinking of.
>
> I blame the 'flu.
>
> I have to blame someone!!
>
> Thanks for the correction.
>
> Regards
> HJR
Actually, I'm curious about the other question -- how common is it to
want foreign keys that refer to keys that aren't unique? Oracle
already allows foreign keys to refer to deferred unique keys, which
make use of nonunique indexes. That knows not to check for orphaned
foreign keys when it's only a duplicate being deleted. Given that, a
foreign key referencing a key that isn't unique doesn't seem like too
big a stretch.
Received on Tue Mar 23 2004 - 11:56:49 CST
