alter table to add primary key in SQL Server

Alter Table command in SQL Server:

If you have created a table without a Primary Key and at a later stage, you realized that you need a Primary Key, you can use alter table command to create Primary Key.

Create a table using below command in SQL Server:


CREATE TABLE tblEmployee
(empID INT NOT NULL,
empName VARCHAR(30) NOT NULL,
empDept INT)

Now, the table tblEmployee is created, but doesn't contain a Primary Key. Use the following alter command to create the Primary Key.

ALTER TABLE tblEmployee

ADD PRIMARY KEY(empID)

You may get following error if the field you want to alter to PRIMARY KEY is a Nullable field..

Msg 8111, Level 16, State 1, Line 1

Cannot define PRIMARY KEY constraint on nullable column in table 'tblEmployee'.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

First you have to alter the column to make it NOT NULL.

Command to ALTER a Column from NULL to NOT NULL in SQL Server:

ALTER TABLE tblEmployee 

ALTER COLUMN empID INT NOT NULL

Then you can run the alter command to add Primary Key.

Hope this article was useful. Follow my Dotnet Interview Discussion blog at http://dotnetprojectninterviewquestions.blogspot.com/