Do you think IIT Guwahati certified course can help you in your career?
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).
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:
Unique Key
Primary Key
A column with a unique key constraint allows only one NULL value.
A column with a primary key constraint doesn't allow NULL values.
A table can have multiple unique keys.
A table can only have a single primary key.
A unique key generates a non-clustered index.
A primary key generates a clustered index.
We can modify a unique key column.
We cannot modify a primary key column.
It prevents duplicate values.
It is used to identify a record in a table uniquely.
We can use the following syntax to define a unique key:
CREATE TABLE Students (
RollNo int,
Name varchar(255),
Class varchar(255),
Email varchar(255) UNIQUE
);
We can use the following syntax to define a primary key:
CREATE TABLE Students (
RollNo int PRIMARY KEY,
Name varchar(255),
Class varchar(255),
Email varchar(255)
);
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
A 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
A 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.
Frequently Asked Questions
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.
If you want to learn more about DBMS topics and take your basic knowledge on these topics a notch higher, then you can visit our Guided Path for DBMS on Coding Ninjas Studio. To be more confident in data structures and algorithms, try out our DBMS Course. Until then, All the best for your future endeavours, and Keep Coding.
24+ registered 
