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
)
