How to choose the composite primary key for a table

[Madhususdhan]

Hellow, I am new to MS SQL Server and I have been designing a database for my company. I am using Microsoft Visio for desigin and in my database design, I am planning to use composite primary key for a table with the sole reason of avoiding to have a seperate dedicated unique id as a primary key. When I have to use this composite primary key as a Foreign Key in another table, when building the relationship, in the referring table all the coulmns that makes up the composite primary key are getting into the table. Where in fact, I need only one colums as a foreign key on that table and the rest of the columns is just memory overhead. I am not getting how to get around this problem. I am really new to MS SQL and please try to provide me a good solution by benig as informative as possible.

[gpl]

If only one of the columns of your composite primary key is needed to uniquely identify the row (which is what you are saying) then that surely is the primary key ? Any other columns are not needed. If this is not the case, then your foreign key on the oher table could refer to more than 1 row on the master