Difference between Primary Key and Unique Key

Difference between Primary Key and Unique Key

15 Mar 2024
Intermediate
24.9K Views
8 min read
Learn via Video Course & by Doing Hands-on Labs

SQL Server Course

Primary Key and Unique Key: An Overview

In SQL Server, we have two keys that distinctively or uniquely identify a record in the database. Both the keys seem identical, but actually, both are different in features and behaviors. In this SQL Server tutorial, I would like to share the key differences between the primary key and the unique key.

Read More: SQL Server Interview Questions and Answers

What is the Primary key?

A primary key is a set of one or more fields/columns of a table that uniquely identifies a record in a database table. It can not accept null, or duplicate values. It is either an existing table column or a column that is specifically generated by the database according to a defined sequence.

Example of Primary Key

Employee Table

Emp_IdEmp_NameAddressMobile_NoEmail
1SakshiDelhi123456789abc@xyz.com
2SouravHazaribagh223365796jkl@gmail.com
3PragatiNanded175468965ghi@yahoo.com

In the above table, Emp_Id is the primary key. Each employee has a unique ID assigned to them, ensuring that no two employees share the same ID.

What is a Unique Key?

A unique key is a unique value amongst other values that are used to protect duplication of the values in a column of the table. The primary use of a unique key in a table is to prevent duplicate values. But, when it comes to the unique values, the primary key also includes them. So, there is one big difference that makes a unique key different, and it is that the unique key may have a NULL as a value but the primary key does not allow NULL as a value.

Example of Unique Key

Employee Table

Emp_IdEmp_NameAddressMobile_NoEmail
1SakshiDelhi123456789abc@xyz.com
2SouravHazaribagh223365796jkl@gmail.com
3PragatiNanded175468965ghi@yahoo.com

In the Employee relation, a Unique Key could be applied to the email column, allowing null values to be present while maintaining the uniqueness requirement for non-null entries.

For more information about the keys, please refer to the article Different Types of SQL Keys

Difference between Primary Key and Unique Key

Comparison BasisPrimary KeyUnique Key
BasicThe primary key is used as a unique identifier for each record in the table.The unique key is also a unique identifier for records when the primary key is not present in the table.
NULLWe cannot store NULL values in the primary key column.We can store a NULL value in the unique key column, but only one NULL is allowed.
PurposeIt enforces entity integrity.It enforces unique data.
IndexThe primary key, by default, creates a clustered index.The unique key, by default, creates a non-clustered index.
Number of KeyEach table supports only one primary key.A table can have more than one unique key.
Value ModificationWe cannot change or delete values stored in primary keys.We can change unique key values.
UsesThe primary Key is used for indicating the rows uniquely.The Unique Key is used to prevent duplicate entries.

Defining Primary key and Unique key in SQL Server

 
CREATE TABLE Employee 
 (
 EmpID int PRIMARY KEY, --define primary key
 Name varchar (50) NOT NULL,
 MobileNo int UNIQUE, --define unique key
 Salary int NULL
 ) 

Read More: SQL Integrity Constraints

When to Use Primary Key vs. Unique Key

Primary Key Usage Scenarios

  • Uniquely Identifying Records: They mainly ensure that each record in a table can be uniquely identified.
  • Enforcing Entity Integrity: Primary keys enforce entity integrity by ensuring that every row in a table represents a distinct entity or object.
  • Supporting Relationships: By referencing the primary key of one table in another table, we can create associations and maintain referential integrity between the tables.
  • Identifying Parent-Child Relationships: Primary keys help identify the parent record associated with each child record.
  • Enforcing Constraints: Primary keys enforce constraints such as uniqueness and non-nullability, ensuring that certain columns cannot contain duplicate or null values.

Foreign Key Usage Scenarios

  • Maintaining Referential Integrity: The primary purpose of foreign keys is to enforce referential integrity between related tables.
  • Query Optimization: Foreign keys can aid query optimization by providing the query optimizer with additional information about data relationships.
  • Data Integrity Validation: Foreign keys validate the integrity of data during insertion, update, and deletion operations.
  • Cascade Actions: Foreign keys can define cascade actions to maintain data consistency automatically.
  • Navigating Relationships: Foreign keys establish relationships between tables, making navigating and understanding the database schema easier.

Best Practices for Using Primary Key and Unique Key

  • Avoid Composite Primary Keys: If possible, use single-column primary keys rather than composite keys composed of multiple columns.
  • Index Primary and Unique Keys: Automatically or manually create indexes on the primary key and unique key columns to enhance query performance.
  • Maintain Consistency with Foreign Keys: Ensure that foreign keys reference the corresponding primary or unique keys in the parent table, preventing orphaned or inconsistent data.
  • Ensure Uniqueness with Unique Constraints: Use unique constraints or unique keys to enforce uniqueness on one or more columns that are not designated as primary keys.
  • Regularly Review and Validate Key Choices: Periodically review the choice of primary and unique keys to ensure they continue to meet application requirements and data integrity constraints.
Summary

Even though both primary and unique key has a few set of similarities, certainly, primary and unique keys have significant differences such as we can only have one primary key per table, but we are allowed to have multiple unique keys. Same way, a primary key column does not accept NULL values, whereas the unique key columns may contain one null value with each column. And most importantly, the primary key column has a unique clustered index whereas a unique key column has a unique non-clustered index.

I hope you will enjoy these tricks while programming with SQL Server. For practical experience, consider our SQL Server Course.

Do you Know?

.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.

Therefore, if you want to upskill yourselves and stand out from others consider our following training programs on .NET.

FAQs

Q1. What's the difference between a primary key and a unique key?

Primary keys uniquely identify each record in a table and are central to the table's structure, while unique keys are more flexible and can be applied to columns with unique values but not necessarily unique identifiers for records.

Q2. Can a key be both primary and unique?

Yes, a key can be both primary and unique.

Q3. What is the difference between a unique key and a candidate key?

Candidate keys are sets of columns that have the potential to serve as the primary key of a table, while unique keys are constraints that enforce uniqueness within a table but do not necessarily identify each record uniquely.

Q4. Should the primary key be 100% unique?

Yes, a primary key should be 100% unique within its respective table.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE

Share Article

Live Classes Schedule

Our learn-by-building-project method enables you to build practical/coding experience that sticks. 95% of our learners say they have confidence and remember more when they learn by building real world projects.
Generative AI For Software Developers Jul 20 SAT, SUN
Filling Fast
08:30PM to 10:30PM (IST)
Get Details
Angular Certification Course Jul 20 SAT, SUN
Filling Fast
06:00PM to 08:00PM (IST)
Get Details
Azure Master Class Jul 20 SAT, SUN
Filling Fast
03:00PM to 05:00PM (IST)
Get Details
ASP.NET Core Certification Training Jul 28 SAT, SUN
Filling Fast
07:00AM to 09:00AM (IST)
Get Details
Software Architecture and Design Training Jul 28 SAT, SUN
Filling Fast
05:30PM to 07:30PM (IST)
Get Details
.NET Solution Architect Certification Training Jul 28 SAT, SUN
Filling Fast
05:30PM to 07:30PM (IST)
Get Details
Azure Developer Certification Training Jul 28 SAT, SUN
Filling Fast
10:00AM to 12:00PM (IST)
Get Details
Advanced Full-Stack .NET Developer Certification Training Jul 28 SAT, SUN
Filling Fast
07:00AM to 09:00AM (IST)
Get Details
Data Structures and Algorithms Training with C# Jul 28 SAT, SUN
Filling Fast
08:30PM to 10:30PM (IST)
Get Details
Angular Certification Course Aug 11 SAT, SUN
Filling Fast
09:30AM to 11:30AM (IST)
Get Details
ASP.NET Core Project Aug 24 SAT, SUN
Filling Fast
07:00AM to 09:00AM (IST)
Get Details

Can't find convenient schedule? Let us know

About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this