The ideas of a primary key and unique key are essential to relational database design as they guarantee data integrity and define the relationships between tables. Despite their apparent similarity, they have different responsibilities and traits. To help you grasp the distinctions between the Primary Key and Unique Key in SQL databases, this article will provide examples to illustrate each.

Is a Primary Key What?
A table's primary key is a column (or set of columns) that gives each entry in the table a unique identity. It guarantees that the value(s) in the main key column(s) are different in every row. The following guidelines must be followed by the main key:

• 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
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.

Number of Keys per Table

• Primary Key: A table can have only one primary key.
• Unique Key: A table can have multiple unique keys.

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.

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.

HostForLIFEASP.NET SQL Server 2022 Hosting