Archive for November 2013

Difference between primary key and unique key

Hi Friends, in this in this session we are going to learn the differences between primary key and unique key.

This is one of the most frequently asked sql server interview questions.
Before we discuss the difference between primary key and unique key, let’s see what a primary key is and what a unique key is.

Primary Key:
Primary key is a key which uniquely identifies each row/record in the table.

This implies if you have a primary key on a table, you can identify each and every row in that table uniquely using primary key.


Unique Key:
Unique key constraint enforces the uniqueness on the columns in which it is defined.

That is Unique key will make sure only unique values are allowed in the columns in which it is defined.

Similarities:
Both Primary key and unique Key enforces uniqueness on the columns on which they are defined.
  •          Primary key and unique Key columns allow only unique values.
  •          Primary key and unique Key columns will avoid duplicates
  •          Both Primary key and unique Key columns uniquely identifies each row/record in the table


Differences:
PRIMARY KEY
UNIQUE KEY
Primary Key Does not allow duplicates
Unique key allows only one NULL
A table can have only 1 Primary Key
A table can have multiple Unique Keys
By default Primary Key creates a Unique Clustered Index on the table
By default Unique Key creates a Unique Non-Clustered Index on the table


EXAMPLES:
CREATETABLE #CUSTOMER
(
ID          INT               PRIMARYKEY  ,
NAME        VARCHAR(25)                   ,
PHONE       INT               UNIQUE      ,
SSN         INT               UNIQUE           

)

Posted in | Leave a comment