33

How to add composite primary keys in SQL Server 2008?

I have a table as follows.

testRequest (wardNo nchar(5)
            , BHTNo nchar(5)
            , testID nchar(5)
            , reqDateTime datetime);

I need wardNo, BHTNo and testID to be a composite primary key.

How can I do this in SQL Server Management Studio?

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
Dinithi De Silva
  • 1,142
  • 5
  • 28
  • 46
  • possible duplicate of [how do I make a composite key with SQL Server Management Studio?](http://stackoverflow.com/questions/1545571/how-do-i-make-a-composite-key-with-sql-server-management-studio) – Brian Hooper Apr 29 '15 at 14:35

4 Answers4

57

If you use management studio, simply select the wardNo, BHTNo, testID columns and click on the key mark in the toolbar.

enter image description here

Command for this is,

ALTER TABLE dbo.testRequest
ADD CONSTRAINT PK_TestRequest 
PRIMARY KEY (wardNo, BHTNo, TestID)
Mike G
  • 4,232
  • 9
  • 40
  • 66
Madusanka
  • 2,968
  • 5
  • 30
  • 41
40

How about this:

ALTER TABLE dbo.testRequest
ADD CONSTRAINT PK_TestRequest 
PRIMARY KEY (wardNo, BHTNo, TestID) 
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
28

How about something like

CREATE TABLE testRequest (
        wardNo nchar(5),
        BHTNo nchar(5),
        testID nchar(5),
        reqDateTime datetime,
        PRIMARY KEY (wardNo, BHTNo, testID)
);

Have a look at this example

SQL Fiddle DEMO

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
5

it simple, select columns want to insert primary key and click on Key icon on header and save tablesql composite key

happy coding..,