In this blog I will explain some examples that how to work with primary keys in sql server. Here in following examples I will show you that how to create primary key, how to create primary key using alter statement and how to drop primary key using alter statement.

--This is a demonstration of primary key in Sql server.

--Firstly we needs to create a table in which we can apply this demonstration.

--Creating a primary key on column label.

createtable Employee

(

     EmpId varchar(5)notnullconstraint pk1 primarykey,

     EmpRefNo varchar(10)notnull,

     EmpName varchar(50)notnull,

     EmpUId varchar(15)notnull,

     EmpPanNo varchar(20)null

)

--Creating primary key on table label.

createtable Employee

(

     EmpId varchar(5)notnull,

     EmpRefNo varchar(10)notnull,

     EmpName varchar(50)notnull,

     EmpUId varchar(15)notnull,

     EmpPanNo varchar(20)null,

     constraint pk1 primarykey(EmpId)

)

--Creating primary key on multiple columns.

--When we create primary key on more than one column then

--This key is also known as composite key.

createtable Employee

(

     EmpId varchar(5)notnull,

     EmpRefNo varchar(10)notnull,

     EmpName varchar(50)notnull,

     EmpUId varchar(15)notnull,

     EmpPanNo varchar(20)null,

     constraint pk1 primarykey(EmpId,EmpUId)

)

--Creating primary key using alter staement.

--When we create primary key using alter statement

--Then we have to make sure that column have "not null"

--attributes and if it don't have "not null"

--attributes then we needs to assign "not null"

--attribute first using alter statement then we

--create primary key on that.

--Statements to create Employee table.

createtable Employee

(

     EmpId varchar(5)notnull,

     EmpRefNo varchar(10)notnull,

     EmpName varchar(50)notnull,

     EmpUId varchar(15)notnull,

     EmpPanNo varchar(20)null,

)

--Statements to create primary key using alter statement.

altertable Employee

addconstraint pk1 primarykey(EmpId)

--Statements to drop primary key using alter statement.

altertable Employee

dropconstraint pk1

Thanks.