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.

create table Employee

(

     EmpId varchar(5) not null constraint pk1 primary key,

     EmpRefNo varchar(10) not null,

     EmpName varchar(50) not null,

     EmpUId varchar(15) not null,

     EmpPanNo varchar(20) null

)

--Creating primary key on table label.

create table Employee

(

     EmpId varchar(5) not null,

     EmpRefNo varchar(10) not null,

     EmpName varchar(50) not null,

     EmpUId varchar(15) not null,

     EmpPanNo varchar(20) null,

     constraint pk1 primary key(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.

create table Employee

(

     EmpId varchar(5) not null,

     EmpRefNo varchar(10) not null,

     EmpName varchar(50) not null,

     EmpUId varchar(15) not null,

     EmpPanNo varchar(20) null,

     constraint pk1 primary key(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.

create table Employee

(

     EmpId varchar(5) not null,

     EmpRefNo varchar(10) not null,

     EmpName varchar(50) not null,

     EmpUId varchar(15) not null,

     EmpPanNo varchar(20) null,

)

--Statements to create primary key using alter statement.

alter table Employee

add constraint pk1 primary key(EmpId)

--Statements to drop primary key using alter statement.

alter table Employee

drop constraint pk1

Thanks.