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

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.

However, then t1 (id) would be unique. If you don't want t1 (id) to be unique, then the answer is no, you can't do this.

Is it a common thing to want a foreign key that references a key that's not unique? Received on Thu Mar 18 2004 - 11:41:55 CST