1.
Introduction
2.
What is a Unique Key?
2.1.
Code
2.2.
Output
2.3.
Explanation
3.
What is a Primary Key?
3.1.
Code
3.2.
Output
3.3.
Explanation
4.
Comparison Table between Unique and Primary Key
5.
Features of Unique key
6.
Properties of Unique Key
6.1.
Multiple Unique Keys
6.2.
Allows a NULL Value
6.3.
Non-Clustered Index
7.
Features of Primary key
8.
Properties of Primary Key
8.1.
Single Primary Key
8.2.
No NULL Values
8.3.
Clustered Index
9.
9.1.
Is this a unique example of a primary key?
9.2.
Can the primary key be NULL?
9.3.
Can a primary key have duplicates?
9.4.
Can a unique key always be used as a primary key?
10.
Conclusion
Last Updated: Mar 27, 2024
Easy

# Difference between Unique and Primary Key

## Introduction

Key constraints play a vital role in relational databases. They are used to identify records uniquely and relate different tables with each other. The most commonly used keys are the primary and the unique keys, and choosing the correct one is crucial. This article will explore the differences between unique and primary keys and their properties.

So, without further ado, let’s begin!

## What is a Unique Key?

A unique key is a group of one or more fields of a relation that uniquely identifies any record. Thus, any column with a unique key constraint cannot contain duplicate values. This key enforces that a column or a group of columns always contains unique values.

For example, let’s say we have a table named “Students” with columns “RollNo”, “Name”, “Class”, and “Email”. “RollNo” and “Email” can be unique keys in this table.

Let us look at the use of unique keys using SQL.

### Code

``````-- Create a table
CREATE TABLE Students (
RollNo int,
Name varchar(255),
Class varchar(255),
Email varchar(255) UNIQUE
);
-- Insert some values
INSERT INTO Students VALUES(1, 'A', 'X', 'abc@gmail.com');
INSERT INTO Students VALUES(2, 'B', 'XI', 'def@gmail.com');
INSERT INTO Students VALUES(3, 'C', 'XI', null);
INSERT INTO Students VALUES(4, 'D', 'XII', 'def@gmail.com');
-- Fetch values
SELECT * FROM Students;``````

### Output

``````Error: near line 12: stepping, UNIQUE constraint failed: Students.Email (19)

1|A|X|abc@gmail.com

2|B|XI|def@gmail.com

3|C|XI|

[Execution complete with exit code 1]``````

### Explanation

We can observe that the unique key allows us to insert a NULL value but does not allow duplicate value (it gave an error).

Also see, SQL EXCEPT

## What is a Primary Key?

A primary key is also a group of one or more fields of relation that uniquely identifies any record. Although the primary and unique key constraints do similar things, columns defined as primary keys cannot contain NULL values, and any table can only have one primary key.

For example, let’s use the previous table named “Students” with columns “RollNo”, “Name”, “Class”, and “Email”. Only the “RollNo” field from this table can be defined as a primary key because each student will have a unique roll number, while some students may not have an email address.

### Code

``````-- Create a table
CREATE TABLE Students (
RollNo int PRIMARY KEY,
Name varchar(255),
Class varchar(255),
Email varchar(255)
);
-- Insert some values
INSERT INTO Students VALUES(1, 'A', 'X', 'abc@gmail.com');
INSERT INTO Students VALUES(2, 'B', 'XI', 'def@gmail.com');
INSERT INTO Students VALUES(2, 'C', 'XI', 'ghi@gmail.com');
-- Fetch values
SELECT * FROM Students;``````

### Output

``````Error: near line 11: stepping, UNIQUE constraint failed: Students.RollNo (19)

1|A|X|abc@gmail.com

2|B|XI|def@gmail.com

[Execution complete with exit code 1]``````

### Explanation

We can observe that the primary key also gives an error if we try to insert duplicate values.

## Comparison Table between Unique and Primary Key

The following table shows the differences between a unique and primary key:

Recommended topics, Schema in DBMS and  Tcl Commands in SQL

## Features of Unique key

Below are the key features of unique keys:

• Uniqueness: Unique keys must be unique for each record in the table. This means that no two records can have the same value for the unique key

• Non-null: Unique keys cannot contain null values. This is because null values are not unique

• Candidate key: A unique key is a candidate key if it can uniquely identify each record in the table. A table may have multiple candidate keys, but only one of them can be the primary key

• Alternate key: A unique key that is not the primary key is called an alternate key. Alternate keys can be used to enforce uniqueness on a column or set of columns that is not the primary key

• Foreign key reference: Unique keys can be referenced by foreign keys in other tables. This allows you to create relationships between tables and to ensure that the data in the tables is consistent

## Properties of Unique Key

### Multiple Unique Keys

A table can have multiple unique keys. Each unique key can be defined on one or more columns in the table, and each key must have a unique name within the table.

### Allows a NULL Value

If a column has a unique key constraint, at most, one record is permitted to have a NULL value for that column.

### Non-Clustered Index

Non-Clustered Index is automatically generated when a unique key is defined. A non-clustered index is an index that is separate from the actual data in the table.

## Features of Primary key

Below are the key features of Primary keys:

• Uniqueness: Each row in the table must have a unique value for the primary key

• Non-null: The primary key cannot contain null values

• Candidate key: The primary key must be a candidate key, which means that it must be able to uniquely identify each row in the table

• Foreign key reference: The primary key can be referenced by foreign keys in other tables

## Properties of Primary Key

### Single Primary Key

A table can only have a single primary key. It enforces data integrity and ensures that each row in the table is unique.

### No NULL Values

If a column has a primary key constraint, no record is allowed to have a NULL value for that column.

### Clustered Index

Clustered index is automatically generated when a primary key is defined. A clustered index is a database index used to determine the physical order of data in a table.

### Is this a unique example of a primary key?

Yes, a primary key is a unique identifier for each record in a database table, ensuring each row has a distinct value.

### Can the primary key be NULL?

No, a primary key cannot be NULL, and it must have a unique, non-null value.

### Can a primary key have duplicates?

No, a primary key cannot have duplicates, and it enforces uniqueness for each record.

### Can a unique key always be used as a primary key?

We cannot always use a unique key as a primary key because a primary key needs to satisfy more requirements. A primary key must always have a value (cannot be NULL) and must not change. Also, a table can only have a single primary key, whereas it can have multiple unique keys.

## Conclusion

This article discussed the difference between unique and primary keys and their properties. It is essential to understand this difference so you can choose the right key according to your needs.