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.