Foreign Key as subset of Primary Key

Foreign Key as subset of Primary Key

Post by Tom Hummel, International Superst » Tue, 30 Jun 1998 04:00:00




>MS SQL Server gives an error msg 1776 if foreign key is referencing
>part of a primary key.

>e.g.
> Master Table
>              Address (Address_id   int,
>                       Seq_no       identity,
>                       Descr        varchar(50)) with PK (Address_id, Seq_no)

> Dependant table
>                 Contact (Contact_id   int,
>                          Address_id   int,
>                          Contact_name  varchar(30)) with PK Contact_id

>I want to make Foreign Key on Contact(Address_id) Referencing
>Address(Address_id). Can someone HELP ????

>PS: don't ask to add a column Seq_no in Contact table

I don't believe that this is possible any other way. A foreign key
requires that the referenced table be unique within the reference
columns. Since your Address records are not necessarily unique by just
the Address_ID, you can't create a foreign key on just that column. In
short, what you're trying to get is not a foreign key. What are you
trying to accomplish by putting that foreign key in? Better access
times? In that case, try just adding an index on that field.

        -Tom.

 
 
 

Foreign Key as subset of Primary Key

Post by Hung Bin » Thu, 02 Jul 1998 04:00:00


I agree with this comment.  However, if the real purpose is referential
integrity for the two concerned tables, one a create a trigger on the
Address table to update, insert, delete row in the Contact table.
HB

>I don't believe that this is possible any other way. A foreign key
>requires that the referenced table be unique within the reference
>columns. Since your Address records are not necessarily unique by just
>the Address_ID, you can't create a foreign key on just that column. In
>short, what you're trying to get is not a foreign key. What are you
>trying to accomplish by putting that foreign key in? Better access
>times? In that case, try just adding an index on that field.

> -Tom.