Primary key - things to know (MS sql server )

 create table testmercando1

(

ID int not null,

--rollno not null primary key, -- can not define two primary keys this way 

name varchar(50),

section varchar(1))

NOTE:To drop and add primary key after table creation .. constraint keyword and primary key constraint name should be used extensively

COMPOSITE PRIMARY KEY:

YOU CAN ONLY HAVE PRIMARY KEY IN A TABLE BUT IF YOU WANT TWO OR MORE PRIMARY KEYS YOU NEED TO MAKE COMPOSITE PRIMARY KEY WHICH NEEDS EQUAL NUMBER OF FOREIGN KEYS(if foreign key needed) IN CHILD TABLE. you may want to make two to three primary keys in one table, but regardless of how many primary key you want you can define only one primary key constraint, cant make different primary key constraint even if you want different name. the error is showin in the snapshot.

correct way of above snapshot is:

alter table testmercando1 add constraint pk_mercandocando primary key(ID,rollno) --right way BUT THIS WILL BE NOW A COMPOSITE PRIMARY KEY MEANING IT REQUIRES EQUAL NUMBER OF FOREIGN KEY ASSIGNED IN EVERY CHILD TABLE. YOU CAN NOT LINK ONE PRIMARY KEY WITH ONE FOREIGN KEY BOTH KEYS ARE COMPULSORY TO HAVE EQUAL NUMBER OF FOREIGN KEYS. FOLLOWING IS THE CODE FOR COMPOSITE KEY'S RELATIONSHIP.

-- dropping primary key 

alter table testmercando1 drop primary key -- wrong 

alter table testmercando1 drop constraint PK__testmerc__3214EC27324AA928-- right 

-- what if i need two primary keys id and new colomn roll number as pk

alter table testmercando1 add column rollno int not null, --wrong

alter table test mercando1 add rollno int not null-- right

alter table testmercando1 add constraint PK_Cando1 primary key (ID,rollno)

drop table testmercando1

-- for single primary key we can also do 

alter table testmercando1 add primary key (ID) -- but giving a consraint name for id is preferd for more systematic

alter table testmercando1 add constraint pk_mercandocando primary key(ID)-- like this 

Syntax--

create table table_name

(

 id int not null primary key,

other columns)

adding primary key after table creation 

alter table table_name add constraint Pk_giveyourpkconstraint_name primary key (ID,otherprimary key if you want with comma)

dropping primary key 

alter table table_name drop constraint PK_pkconstraint name --therefore it is more systematic to give constrant name while creating pk will help for dropping pk latter EASILY