How to set (combine) two primary keys in a table

Question

For a small sales related application, we designed database using logical data model. Come to the stage to convert in to physical model. While creating table in SQL Server Management Studio Express, according to our logical data model, we need to combine two attributes to form unique id. Is it possible to combine two primary keys and set it?

But while observing Northwind Sample, we found that in the ORDER DETAILS table, we can see two primary keys Order Id & Product Id. And according to rule table can't have two primary keys. So how it happened in Northwind?

In my case how should I set two columns in my table to make it as two primary keys?

Someone gave suggestion like

To make a two primary key, open the table in design view and click on the two of the required fields and holding CTL, apply primary key

Will this work ??

Edit ;

now my table have 2 PK's in the SSMS . is it valid or it is just a combinationof 2 pks

Solution 1

The easiest way would be to use a T-SQL command in SSMS Express rather than trying to use the visual designers.....

Once you've designed and created your table, try something like this:

ALTER TABLE dbo.YourTableNameHere
ADD CONSTRAINT PK_YourTableNameHere
PRIMARY KEY(Item_Id, Purchase_Id)

Solution 2

you cannot create two primary keys in a table. You can combine two columns in a table and make as a single primary key

create table table1
(
col1 int,
col2 varchar(20),
....
Primary key (col1, col2)
)

Comments

[marc_s]

Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! Then you don't need all those messy <p> and <br/> tags, either!

[panindra]

How to use this in SQL then ?? for example in " Select * from Stack where _____? . how to use this combination –

[marc_s]

@panindra: use your regular SQL as always - just because the primary key has two columns doesn't change anything at all ..... SELECT (list of cols) FROM dbo.Stack WHERE ...(whatever condition you need here)...

[panindra]

Did you mean to say i can either one of the combined keys in the Where to identify the row or i can use combination of keys . is it ??

[marc_s]

@panindra: you can use anything you like to search! There's no reason why you would have to only use the primary key columns to search...... or what are you unsure about?? The primary key just defines that the combination of those two columns can uniquely and safely identify each separate row - no two rows will have the same combination of (Item_Id, Purchase_Id) - that's all the primary key enforces...

[marc_s]

@panindra: if you need to find that one row defined by Item_Id and Purchase_Id, then you'd just use SELECT (cols) FROM dbo.Stack WHERE Item_Id = 4711 AND Purchase_Id = 501992 or whatever you need.... since the two columns together are the primary key, you are guaranteed to either get nothing (if that combination of values doesn't exist), or you get the one and only row that matches those two values. There can NEVER be more than one row for those two values.

[panindra]

,thnx . this is what i am looking ( how to use combined keys in the sql ) thnks for your support