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.