Please enable Javascript for better experience...
Adding identity and primary key constraint on existing table in Sql Server
By Big | Jan 6, 2015 | In Tips | Update: Jan 6, 2015 | Total Views [ 1073 ]
Taged In
(1 Like)
Rate

Sometime ago I was working on a project and created a table. This table got 100 of records in one day and suddenly I remember that I forgot to put primary key and identity on it. Here I will show to add identity and primary key constraint on existing table in Sql Server.

Introduction

Adding primary key with identity column is not so tough. Let's see how to add this on an existing table.

Create table without any constraint

Let's create a table first without a primary key.

CREATE TABLE MyTable(FirstName varchar(50), LastName varchar(50))

Alter table to add identity and primary key

As you can see, there is no constraint defined on this table. Now let's add identity and primary key on it. You can do this with two simple commands.

--This command will create idenity column and automatiaclly add values for all the available rows
ALTER TABLE MyTable ADD ID INT IDENTITY
GO
ALTER TABLE MyTable ADD CONSTRAINT PK_ID PRIMARY KEY (ID)

Hope this helps you.

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