Post
Ask Question

Difference Between Primary Key and Unique Key in SQL

In relational database design, the concepts of Primary Key and Unique Key are crucial for ensuring data integrity and establishing the relationships between tables. Although they may seem similar, they have distinct roles and characteristics. This article will explain the differences between the Primary Key and Unique Key with examples to help you understand their importance in SQL databases.

What is a Primary Key?

A Primary Key is a column (or a combination of columns) in a table that uniquely identifies each row in that table. It ensures that no two rows have the same value(s) in the primary key column(s). The primary key must adhere to the following rules:

  • Uniqueness: Every value in the primary key column(s) must be unique.
  • Not Null: The primary key column(s) cannot contain NULL values.
  • Single Primary Key: A table can have only one primary key.

Example of Primary Key

Consider a table called Employees that stores information about employees in a company.

CREATE TABLE Employees (
    EmployeeID INT NOT NULL PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100)
);

In this example, the EmployeeID column is the primary key. Each employee has a unique EmployeeID, ensuring that no two employees can have the same identifier.

What is a Unique Key?

A Unique Key is a constraint that ensures all values in a column (or a set of columns) are unique across the database. The unique key enforces uniqueness but, unlike a primary key, it can accept one or more NULL values.

  • Uniqueness: Each value in the unique key column(s) must be unique.
  • Allows NULLs: A unique key can have NULL values, but only one NULL value is allowed in a column.
  • Multiple Unique Keys: A table can have multiple unique keys.

Example of Unique Key

Let's extend the Employee's table to ensure that no two employees can have the same email address.

CREATE TABLE Employees (
    EmployeeID INT NOT NULL PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100) UNIQUE
);

In this example, the Email column is defined as a unique key. This constraint ensures that every email address in the Employees table is unique, but it allows one record to have a NULL email address.

Key Differences between Primary Key and Unique Key

  1. Uniqueness and NULL Handling
    • Primary Key: Ensures uniqueness and does not allow NULL values.
    • Unique Key: Ensures uniqueness but allows one NULL value per column.
  2. Number of Keys per Table
    • Primary Key: A table can have only one primary key.
    • Unique Key: A table can have multiple unique keys.
  3. Usage
    • Primary Key: Used to uniquely identify each record in a table and often used in defining relationships between tables (e.g., foreign keys).
    • Unique Key: Used to ensure that specific columns have unique values across the table, such as email addresses or social security numbers.
  4. Index Creation
    • Primary Key: Automatically creates a clustered index (if the table does not already have one).
    • Unique Key: Creates a non-clustered index by default.

Real-World Scenario

Imagine you're designing a database for a school. You have a Student table, and you want to ensure that each student has a unique student ID and that no two students have the same email address.

CREATE TABLE Students (
    StudentID INT NOT NULL PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100) UNIQUE,
    PhoneNumber NVARCHAR(15) UNIQUE
);
  • StudentID is the primary key, uniquely identifying each student.
  • Email and phone numbers are unique keys, ensuring that each student has a unique email and phone number.

Conclusion

The Primary Key and Unique Key are both essential for maintaining data integrity in SQL databases, but they serve different purposes. The primary key uniquely identifies each record and does not allow NULL values, whereas the unique key ensures uniqueness in a column but can accept a NULL value. Understanding these differences helps in designing efficient and reliable database schemas.

These concepts are fundamental in database management, and mastering them is crucial for anyone working with relational databases.


Similar Articles