Post
Ask Question

Difference Between Unique Key And Primary Key

Primary keys and unique keys are commonly used in relational databases. In this blog, I will discuss basics of Primary keys and Unique keys, the difference between a primary key and unique key, and the similarities between the two.
 
So, let's first start from what are the similarities between these two and after that, the syntax, and by the end of this blog, you yourself will able to differentiate between these two.
 
So, here we go. 

Similarities between Primary Keys and Unique Keys

 
Sr. No.
UNIQUE KEY
PRIMARY KEY
1
Unique key is used to identify a record uniquely. Let me explain clearly. If you are making any column in the table as a unique key, you will be able to filter the data uniquely by using the unique key column name.
In the same manner, you can use the primary key to filter the data uniquely. It will give you the same result.
 

UNIQUE KEY

 
How to declare any column as Unique key?
  1. CREATE TABLE FirstBlog (  
  2.    ID int NOT NULL UNIQUE,  
  3.    LastName varchar(200) NOT NULL,  
  4.    FirstName varchar(200),  
  5.    Salary int  
  6. );  
Desc
 
NOT NULL is a constraint. This means that the column ‘ID’ will not accept null values. By adding the UNIQUE constraint, we are making the ID column as the unique key, while the other column description will go as is. Like Lastname with varchar datatype will not accept NULL values but the column FirstName can accept NULL values as we have not marked any constraints and Salary will also accept NULL but integer in datatype.
 
Note
For the different types of constraints and datatypes in SQL server, I will write another blog.
 
How to declare more than one columns as Unique key
  1. CREATE TABLE FirstBlog (  
  2.    ID int NOT NULL UNIQUE,  
  3.    LastName varchar(200) NOT NULL,  
  4.    FirstName varchar(200),  
  5.    Salary int,  
  6.    CONSTRAINT UC_ FirstBlog UNIQUE (ID,LastName)  
  7. );  
In the above representation of the declaration of the UNIQUE key on multiple columns, we can use the CONSTRAINT keyword. UC_ FirstBlog is the new name of the combination of two columns. One thing you can see in the above example is that we are able to declare two UNIQUE keys on the table FirstBlog.
 
How to add UNIQUE key in an existing table
 
Let's take the above example with some corrections.
  1. CREATE TABLE FirstBlog (  
  2.    ID int ,  
  3.    LastName varchar(200) NOT NULL,  
  4.    FirstName varchar(200),  
  5.    Salary int  
  6. );  
Execute it.
 
Table name with FirstBlog will get created in the database.
 
Now, if after that, we want to add a UNIQUE key on ID column, then we need to ALTER the table. See below,
  1. ALTER TABLE FirstBlog  
  2. ADD UNIQUE(ID);  
In the same way, we can do this for multiple columns to make them a UNIQUE key. See below,
  1. ALTER TABLE FirstBlog  
  2. ADD CONSTRAINT UC_FirstBlog UNIQUE(ID, LastName;)  
One more condition; if anyone wants to delete the UNIQUE key constraint on the table, we can use this syntax.
  1. ALTER TABLE FirstBlog  
  2. DROP UNIQUE(ID);  

PRIMARY KEY

 
How to declare column as a Primary Key
  1. CREATE TABLE FirstBlog (  
  2.    ID int NOT NULL PRIMARY KEY,  
  3.    LastName varchar(200) NOT NULL,  
  4.    FirstName varchar(200),  
  5.    Salary int  
  6. );  
Desc
 
NOT NULL is a constraint which means column ‘ID’ will not accept null values, and by adding a PRIMARY KEY constraint, we are making ID column as primary key and other column description will go so on. ... Like Lastname with varchar datatype will not accept NULL values but the column FirstName can accept NULL values as we have not marked any constraints and Salary will also accept NULL but integer in datatype.
 
How to declare more than one column as Primary Key
  1. CREATE TABLE FirstBlog (  
  2.    ID int NOT NULL ,  
  3.    LastName varchar(200) NOT NULL,  
  4.    FirstName varchar(200),  
  5.    Salary int,  
  6.    CONSTRAINT PK_ FirstBlog UNIQUE (ID,LastName)  
  7. );  
In the above representation of the declaration of Primary key on multiple columns, we can use the CONSTRAINT keyword. PK_ FirstBlog is the new name of the combination of two columns. Note that we can only create one primary key on a single table.
 
How to add PRIMARY key in an existing table
 
Let's take the above example with some corrections.
  1. CREATE TABLE FirstBlog (  
  2.    ID int ,  
  3.    LastName varchar(200) NOT NULL,  
  4.    FirstName varchar(200),  
  5.    Salary int  
  6. );  
Execute it.
 
Table name with FirstBlog will get created in the database.
 
Now, if after that we want to add a PRIMARY key on the ID column, then we need to ALTER the table. See below.
  1. ALTER TABLE FirstBlog  
  2. ADD PRIMARY KEY(ID);  
In the same way, we can make multiple columns as PRIMARY key. See below.
  1. ALTER TABLE FirstBlog  
  2. ADD CONSTRAINT PK_FirstBlog PRIMARY KEY(ID, LastName;)  
One more condition; if anyone wants to delete the PRIMARY key constraint on the table, we can use this syntax.
  1. ALTER TABLE FirstBlog  
  2. DROP PRIMARY KEY;  
Note
In the above example, we have not mentioned any column name for dropping the PRIMARY key because there is only one primary key allowed for the table. 
 

Differences betwee Primary Keys and Unique Keys

 
Sr. No.
UNIQUE KEY
PRIMARY KEY
1
As the name suggests it is used to filter records with the unique id but it accepts a NULL value.
A primary key is also used to filter records uniquely but doesn’t allow NULL value
2
Creates a Non-Clustered Index.
Creates Clustered Index
3
Only one NULL value allowed
NULL values not allowed
4
Can create multiple UNIQUE keys on the table.
Only one primary key is allowed for a table
 
I tried to keep it simple and understandable.
 
Your comments, corrections, and appreciation, all are welcome.
 
Thank you!!