Primary Key vs Unique Key: What's the Difference?

Details

What is Primary Key?

A primary key constrain is a column or group of columns in a table that uniquely identifies every row in that table. The Primary key can't be a duplicate, meaning the same value can't appear more than once in the table.

A table should have more than one primary key. Primary key can be defined at the column or the table level. If you create a composite primary key, it should be defined at the table level.

In this tutorial, you will learn:

What is Unique Key?

A unique key is a group of one or more than one fields or columns of a table which uniquely identify database record.

A unique key is the same as a primary key, but it can accept one null value for a table column. It also cannot contain identical values. Unique constraints are referenced by the foreign key of other tables.

KEY DIFFERENCES

  • There can be one primary key in a table while there can be multiple unique keys in the table.
  • The purpose of the primary key is to enforce entity integrity on the other hand the purpose of unique key is to enforce unique data.
  • In primary key, default Index is clustered whereas in unique key, default index is not-clustered
  • Primary key does not allow null columns whereas unique allows null columns.
  • In the primary key, duplicate keys are not allowed while in a unique key, if one or more key parts are null, then duplicate keys are allowed.

Why use Primary Key?

Here are the important reasons to use primary key:

Why use Unique Key?

Here are the important reasons to use unique key:

Features of Primary Key

Here, are the important features of primary key:

Features of Unique key

Here, are the important features of unique key:

Example of Creating Primary Key

The following example describes that there is a table called student. It contains five attributes, 1) StudID, 2) Roll No, 3) First Name, 4) Last Name, and 5) Email.

The Roll No attribute can never contain a duplicate or null value. It is because every student enrolled in a university can have unique roll number. You can easily identify each row of a table with student's roll number. So it is considered as a primary key.

Primary Key example

Example of Creating Unique Key

Consider the same student table with attributes, 1) StudID, 2) Roll No, 3) First Name, 4) Last Name, and 5) Email.

Stud ID can have a unique constraint where entries in Stud ID column can be unique because each student of a university must have a unique ID number. In case, if student is changing the university, in that case, he or she would not have any stud ID. The entry may have a null value as only one null is allowed in the unique key constraint.

Unique key example

Difference between Primary key and Unique key

Here are the important differences between primary key and unique key:

Primary Key Unique Key
There can be one primary key in a table There can be multiple unique keys in the table
It does not allow null columns. It allows null columns.
Default Index is clustered Default Index is no-clustered
The purpose of the primary key is to enforce entity integrity. The purpose of unique key is to enforce unique data.
Primary key can be created using syntax: 
CREATE TABLE Employee
(
ID int PRIMARY KEY, 
Name varchar(255), 
City varchar(150)
)
Unique key can be created using syntax: 
CREATE TABLE Employee
(
ID int UNIQUE.
Name varchar(255) NOT NULL. City varchar(150)
)
It is SQL constraint which allows you to uniquely identify each record or row in the database table. It is SQL constraint that doesnot allow the same value tobe assigned to two isolatedRecords in a database table.
In the primary key, duplicate keys are not allowed. In a unique key, if one or more key parts are null, then duplicate keys are allowed.

What is better?

 

YOU MIGHT LIKE: