If somebody ask you that can you create primary key on more than one column then what would be your answer. Your simple answer should be "YES, I can". We can create composite primary key which can include more than one columns of a table.

Introduction

Composite Primary key can be defined as group of more than one column which can be used as a unique identifier for each record in a database table. Let's see how to add a composite primary key constraint while creating a new table or to an existing table in Sql Server. Let's see it one by one.

Create command

You can add composite primary key constraint while creating new table using create command. Eah column which is used as a part of composite primary key should be NOT NULL.

CREATE TABLE MyTable(ID int NOT NULL, Name varchar(50) NOT NULL, FName varchar(50), LName varchar(50), CONSTRAINT PK_ID_NAME PRIMARY KEY(ID,Name))

Alter command

We can also use alter command to add composite primary key on an existing table like this.

ALTER TABLE MyTable ADD CONSTRAINT PK_ID_NAME PRIMARY KEY (ID,Name)

ALTER TABLE MyTable ADD CONSTRAINT PK_ID_NAME PRIMARY KEY (ID,Name)
and
ALTER TABLE MyTable ADD CONSTRAINT PK_ID_NAME1 PRIMARY KEY (Name,ID)
Are not same.Both are different composite keys.

You can also delete this constraint using alter command. For more info please click on the link.

Hope it helps you. Use it in your code and enjoy.