Wednesday 31 May 2017

Difference between PRIMARY KEY and UNIQUE KEY

Below are the difference between the Primary and Unique Key

We can create multiple unique keys in the table but only one primary key

See the example
Here I am creating a table which has only one primary key
Create table Emp
(
ID int primary key,
Name varchar(50),
SSN int,
Mobile_Number int
)
Table creates successfully.
 
Now I am trying to create one more primary key on SSN. See below
Getting below error
 
It means we can’t create multiple Primary key in a table.
Now I am creating the Unique key in the table.
 
Two unique key I created. It means we can create n number of unique key in a table.

Unique Keys can have a null value but the primary keys can’t have nulls

See the below example
Primary key can’t have a null value.
 
Now again I want to insert a records with ID=1.
Throwing below error
 
When we trying to insert NULL value in primary key getting below error.
  

In unique key column one null will accept see below

 

A unique column has only one null value. We can’t insert the multiple null values in the table.  
 

When we trying to insert duplicate value in unique key column vale getting below error
 

By default Indexes

Primary key create clustered index by default. But Unique creates a non-clustered index.
See the example
With the help of below SQL script we will get the table information
SP_HELP Emp

 
 


Popular Posts