Difference Between Primary key and Unique key

ByRichard Peterson Hours Updated

Key Difference between Primary Key and Unique key

  • 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 the Unique key 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.

Primary Key vs Unique key

Primary Key vs Unique key

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 can have only 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.

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.

Primary Key vs Unique key – Difference Between Them

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.

Why use Primary Key?

Here are the important reasons to use primary key:

  • The main aim of the primary key is to identify each and every record in the database table.
  • You can use a primary key when you do not allow someone to enter null values.
  • If you delete or update a record, the action you specified will be undertaken to make sure database data integrity.
  • Perform restrict operation to rejects delete or update operation for the parent table.
  • Data are organized in sequence of clustered index whenever you physically organize DBMS table.

Why use Unique Key?

Here are the important reasons to use unique key:

  • The purpose of a unique key is to make sure that information in the column for each table record is unique.
  • When you allow the user to enter the null value.
  • Unique key is used because it creates a non-clustered index by default.
  • Unique key can be used when you have to keep null values in column.
  • When one or more than one field/columns of a table that uniquely identify a record in a database table.

Features of Primary Key

Here, are the important features of primary key:

  • The primary key implements the entity integrity of the table.
  • You can keep only one primary in the table.
  • The primary key contains of one or more table columns.
  • Columns are defined as not null.

Features of Unique key

Here, are the important features of unique key:

  • You can define more than one unique key in the table.
  • By default, unique Keys are in non-clustered unique indexes.
  • It constitutes of one or more table columns.
  • The table column can be null, but only one null per column is preferable.
  • A unique constraint can be easily referenced by a foreign key constraint.

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

Primary key vs Unique key: What is better?

  • Unique key is better when you have columns you know shouldn’t contain duplication. This becomes a good way to make sure data validation.
  • Primary key is ideal when you cannot keep null in the table. It can also use when you have a foreign key in another table for creating a relationship.

You Might Like: