What is Primary key?

Primary key is used to uniquely identify a row in a table. A table can have only one primary key. Primary keys don’t allow null values. Primary key will create clustered index by default. It can be defined at the column level or at the table level.
Primary Key can be defined while creating a table with Create Table command or it can be added with the Alter table command.

Primary key defined at the column level:
When we define a primary key at single column then it is called Column-level primary key.
create table tblStudent(studentId int primary key,studentName nvarchar(100), Departmenttid int)

Primary key defined at the table level:
when we define a composition key on two or more than two columns then it is called table-level primary key.

‘OR’
If a primary key is a combination of two or more than two columns then it can only be defined at the table level only.

create table tblStudent(studentId int, studentName nvarchar(100), Departmenttid int , primary key (studentId, DepartmentId))

Adding Primary Key constraint using Alter table command:

Suppose there is no primary key defined for the table tblStudent and we want to add a primary key constraints on the column studentId.

 The query for Adding a primary key with the Alter Table command is as follows:-
Syntax:
Alter Table tablename Add constraint constrainname Primary Key (Columnname)
Example:
Alter Table tblStudent add constraint  pk_StuPrimaryKey  primary key(studentId)

Dropping a primary constraint from a table :

Syntax:
Alter Table tablename Drop constraintname

Example:
alter table tblStudent  drop constraint  pk_ StuPrimaryKey

Advantages:
1. It is a unique key on which all the other candidate keys are functionally dependent.
2. It prevents to enter null data.
3. It prevents to enter duplicate data.
4. It helps to force integrety constraints.

Disadvantage:
On primary key index will be created so during updation of table index need to be adjusted accordingly. this process makes the updation slower