Please enable Javascript for better experience...
Sql Server-Play with composite primary key
By Big | Jan 5, 2015 | In Tips | Total Views [ 1203 ]
Taged In
(2 Like)
Rate

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.

Share this

About the Author

Big
Rahul Jha (Big)
Founder, Developer dotnet-concept.com
Has working experience in different phases of Software Development Life Cycle (SDLC) in CMS, Gaming, Health Care and Financial Services domain using Agile pattern. Working experience in Design patterns, ASP.NET, MVC, Windows application, WCF, ADO.NET, SQL Server and Test Driven Development (TDD) environment with JQuery, JavaScript, N-Unit, Entity Frameworks, LINQ, Code Refactoring and Business Objects Models.

 
Please SignUp/Login to comment...

Or comment as anonymous...
* Name
* Email ID
Comment