Adding Primary Key On An Existing Table In SQL Server 2008

Posted in SQL | Column Constraints February 26, 2020
Tags: Primary Key, add Primary Key, SQL Server 2008, primary constraint
In this article I will demonstrate how to add primary key to an existing table.
  • 2453

In this article I will demonstrate how to add primary key to an existing table.

Firstly create a table

IF OBJECT_ID ('Employee') IS NOT NULL

DROP TABLE [Employee]

GO

CREATE TABLE [Employee]

(

[id] [int] not null,

[name] [varchar](15)

)

GO

INSERT INTO [Employee]

SELECT 1,'EmpName_1' UNION ALL

SELECT 2,'EmpName_2' UNION ALL

SELECT 3,'EmpName_3' UNION ALL

SELECT 4,'EmpName_4' UNION ALL

SELECT 5,'EmpName_5'

addingPrimaryKey.jpg

Adding Primary Key in Employee table

ALTER TABLE [Employee]

ADD CONSTRAINT PK_Employee_id PRIMARY KEY(id)

addingPrimaryKeyInExistingTable.jpg

Working

In this example, ALTER TABLE is used to add a primary key in an existing table. After that second line defines the constraint name followed by the column defining the key column in parenthesis. Like here we define id as a primary key.

ALTER TABLE [Employee]
ADD CONSTRAINT PK_Employee_id PRIMARY KEY (id)

Note: Before adding PRIMARY KEY constraint to an existing table, ensures that:

  • Existing data should not have duplicate data.
  • Column must have NOT NULL values.
    © 2020 DotNetHeaven. All rights reserved.