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 enter image description here

Your table can only ever have **one** primary key - you probably mean having a primary key with two columns - right? — marc_s, Jul 20 '12 at 11:50
I think you mean "a composite primary key" rather than "two primary keys". In Northwind, Order Id is not unique in Order Details, nor is Product Id. The combination is unique. I think you already know this, but your wording doesn't make it obvious. — Walter Mitty, Jul 20 '12 at 11:55
@marc_s i just attached the screenshot of table in the question, is it just a combination of 2 keys ?? — panindra, Jul 20 '12 at 11:57
@AaronBertrand How to use this in SQL then ?? for example in " Select * from Stack where _____? . how to use this combination — panindra, Jul 20 '12 at 12:01

score 21 · Accepted Answer · answered Jul 20 '12 at 11:52

21

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)

answered Jul 20 '12 at 11:52

marc_s

732,580
175
1,330
1,459

How to use this in SQL then ?? for example in " Select * from Stack where _____? . how to use this combination – – panindra Jul 20 '12 at 12:02
1

@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)...` – marc_s Jul 20 '12 at 12:04
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 ?? – panindra Jul 20 '12 at 12:08
@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 Jul 20 '12 at 12:14
1

@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. – marc_s Jul 20 '12 at 12:18
,thnx . this is what i am looking ( how to use combined keys in the sql ) thnks for your support – panindra Jul 20 '12 at 12:36

score 3 · Answer 2 · answered Jul 20 '12 at 11:52

3

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)
)

answered Jul 20 '12 at 11:52

Joe G Joseph

23,518
5
56
58

score 0 · Answer 3 · answered Jul 20 '12 at 11:50

0

you cant have 2 primary keys but you can have a composite primary key, which is a key made of two columns, which is exactly what you got on the [SalesOrderDetail] table with [SalesOrderID] and [SalesOrderDetailID]

answered Jul 20 '12 at 11:50

Diego

34,802
21
91
134

score 0 · Answer 4 · edited Jul 20 '12 at 11:59

0

CREATE TABLE [dbo].[tab2](  
    [col1] [int] NOT NULL, 
    [col2] [int] NOT NULL, 
    [col3] [nchar](10) NOT NULL,  
    [col4] [nchar](10) NOT NULL,   
 CONSTRAINT [PK_tab2] PRIMARY KEY CLUSTERED    
(  
    [col1] ASC, 
    [col2] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
) ON [PRIMARY]

edited Jul 20 '12 at 11:59

marc_s

732,580
175
1,330
1,459

answered Jul 20 '12 at 11:56

Vikram Jain

5,498
1
19
31

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 `
` and `
` tags, either!
– marc_s Jul 20 '12 at 11:59

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

4 Answers4

Linked

Related