197

I want to create tables in SQL Server 2008, but I don't know how to create composite primary key. How can I achieve this?

SsJVasto
  • 486
  • 2
  • 13
Ganesamoorthy
  • 3,623
  • 8
  • 25
  • 26
  • 1
    As you're new to asp.net, a piece of advice: Composite primary keys are a *bad thing* that usually indicate a poorly thought-out design – smirkingman Oct 13 '10 at 10:12
  • 56
    @smirkingman Very important problems can be solved, often nearly exclusively with composite primary keys. Such as when you have hundreds/thousands of users that save rows to a single table / entity type. You want the rows ordered by user-id, plus then a second value. Your value judgment is simply incorrect, if not, we would be deprecating this feature sometime soon. – Nicholas Petersen Jul 01 '12 at 06:23
  • 2
    Possible duplicate of [Creating composite primary key in SQL Server](http://stackoverflow.com/questions/12594791/creating-composite-primary-key-in-sql-server) – Arun Prasad E S Aug 03 '16 at 08:14

8 Answers8

264
create table my_table (
     column_a integer not null,
     column_b integer not null,
     column_c varchar(50),
     primary key (column_a, column_b)
);
Thilo
  • 257,207
  • 101
  • 511
  • 656
  • 2
    What is the diference between using **Primary Key** and **CONSTRAINT** like in the example by @matthew-abbott ? – mateuscb Oct 06 '11 at 19:57
  • 30
    It creates a named constraint that can be deleted/updated by name – longhairedsi Dec 06 '12 at 14:32
  • 15
    Not quite true. Both create "named constraints". It's just that with the former, you don't control the naming. But once created, you can look up the name that was used and delete/update by name... – Auspex Jul 03 '17 at 15:53
187
CREATE TABLE UserGroup
(
  [User_Id] INT NOT NULL,
  [Group_Id] INT NOT NULL

  CONSTRAINT PK_UserGroup PRIMARY KEY NONCLUSTERED ([User_Id], [Group_Id])
)
Matthew Abbott
  • 60,571
  • 9
  • 104
  • 129
  • 40
    +1 - Yes, name your constraints people, else Sql Server does nasty things like names them PK_UserGrou_5DEAEAF5 in one release's DB and PK_UserGrou_3214EC0777F66C69 on another. This is a pain if you need to update or drop the PK as you have to first get the name from the db and then use dynamic sql (or build the command in code first). Also, it's ugly. – monty Oct 02 '15 at 00:07
  • 2
    Is there any reason I wouldn't want my PK to be clustered? – 4AM May 16 '17 at 14:43
  • 1
    @4AM This may answer your question: [link](https://dba.stackexchange.com/questions/7741/when-should-a-primary-key-be-declared-non-clustered) – Dongminator Jun 08 '17 at 22:53
57

Via Enterprise Manager (SSMS)...

  • Right Click on the Table you wish to create the composite key on and select Design.
  • Highlight the columns you wish to form as a composite key
  • Right Click over those columns and Set Primary Key

To see the SQL you can then right click on the Table > Script Table As > Create To

AmiNadimi
  • 5,129
  • 3
  • 39
  • 55
kevchadders
  • 8,335
  • 4
  • 42
  • 61
37

I know I'm late to this party, but for an existing table, try:

ALTER table TABLE_NAME
ADD CONSTRAINT [name of your PK, e.g. PK_TableName] PRIMARY KEY CLUSTERED (column1, column2, etc.)
Ray K.
  • 2,431
  • 3
  • 25
  • 39
32

For MSSQL Server 2012

CREATE TABLE usrgroup(
  usr_id int FOREIGN KEY REFERENCES users(id),
  grp_id int FOREIGN KEY REFERENCES groups(id),

  PRIMARY KEY (usr_id, grp_id)
)

UPDATE

I should add !

If you want to add foreign / primary keys altering, firstly you should create the keys with constraints or you can not make changes. Like this below:

CREATE TABLE usrgroup(
  usr_id int,
  grp_id int,

  CONSTRAINT FK_usrgroup_usrid FOREIGN KEY (usr_id) REFERENCES users(id),
  CONSTRAINT FK_usrgroup_groupid FOREIGN KEY (grp_id) REFERENCES groups(id),

  CONSTRAINT PK_usrgroup PRIMARY KEY (usr_id,grp_id)
)

Actually last way is healthier and serial. You can look the FK/PK Constraint names (dbo.dbname > Keys > ..) but if you do not use a constraint, MSSQL auto-creates random FK/PK names. You will need to look at every change (alter table) you need.

I recommend that you set a standard for yourself; the constraint should be defined according to the your standard. You will not have to memorize and you will not have to think too long. In short, you work faster.

Freerey
  • 160
  • 2
  • 14
Fatih Mert Doğancan
  • 1,016
  • 14
  • 21
1

First create the database and table, manually adding the columns. In which column to be primary key. You should right click this column and set primary key and set the seed value of the primary key.

ЯegDwight
  • 24,821
  • 10
  • 45
  • 52
Sakthi Thanuskodi
  • 27
  • 1
-3

To create a composite unique key on table 

ALTER TABLE [TableName] ADD UNIQUE ([Column1], [Column2], [column3]);
Abhishek Jaiswal
  • 1,161
  • 12
  • 6
-3
CREATE TABLE UserGroup
(
  [User_Id] INT Foreign Key,
  [Group_Id] INT foreign key,

 PRIMARY KEY ([User_Id], [Group_Id])
)
Abdulla Nilam
  • 36,589
  • 17
  • 64
  • 85
FahadHussain
  • 7
  • 2