Unique Key vs. Primary Key in SQL: A Detailed Guide with Examples

When designing a database, Primary Keys and Unique Keys are two fundamental constraints that ensure data integrity and consistency. Both are essential in structuring a reliable database, but they serve distinct purposes. In this guide, we’ll explore the differences, similarities, and use cases of Primary Keys and Unique Keys, along with practical SQL examples.

Understanding Primary and Unique Keys in SQL

What is a Primary Key?

A Primary Key is a constraint in SQL that uniquely identifies each row in a table. It enforces the following conditions:

• Uniqueness: No two rows can have the same primary key value.
• Not NULL: A primary key cannot contain NULL values.
• Single Instance: A table can have only one primary key.

Example of a Primary Key

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

Here, EmployeeID serves as the Primary Key, ensuring that no two employees have the same ID.

What is a Unique Key?

A Unique Key constraint ensures that values in a column (or combination of columns) remain unique across all rows. Unlike primary keys, unique keys allow one NULL value per column.

Example of a Unique Key

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100) UNIQUE
);

In this example, Email is a Unique Key, ensuring no two employees share the same email address while allowing a NULL value.

Key Differences: Primary Key vs. Unique Key

Feature	Primary Key	Unique Key
Number Per Table	Only one per table	Multiple unique keys allowed
NULL Values	Not allowed	One NULL value per column allowed
Duplicate Values	Not allowed	Not allowed (except for NULL)
Index Type	Automatically creates a Clustered Index	Creates a Non-Clustered Index
Use Case	Identifies each row uniquely	Ensures uniqueness of column values

Implementing Primary Key and Unique Key Constraints

Creating a Table with Both Constraints

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100) UNIQUE,
    PhoneNumber VARCHAR(15) UNIQUE
);

• CustomerID is the Primary Key, uniquely identifying each customer.
• Email and PhoneNumber are Unique Keys, ensuring no duplicate emails or phone numbers exist.

Testing Constraints

1️⃣ Primary Key Violation (Duplicate Value)

INSERT INTO Customers (CustomerID, Name, Email, PhoneNumber) 
VALUES (1, 'Alice Brown', '[email protected]', '1234567890');

INSERT INTO Customers (CustomerID, Name, Email, PhoneNumber) 
VALUES (1, 'Bob White', '[email protected]', '9876543210');
-- ERROR: Duplicate entry for primary key 'CustomerID'

2️⃣ Unique Key Violation (Duplicate Email)

INSERT INTO Customers (CustomerID, Name, Email, PhoneNumber) 
VALUES (2, 'Charlie Green', '[email protected]', '5554443333');
-- ERROR: Duplicate entry for unique key 'Email'

3️⃣ NULL in Unique Key Allowed (Once)

INSERT INTO Customers (CustomerID, Name, Email, PhoneNumber) 
VALUES (3, 'Daniel Grey', NULL, '1112223333');  -- SUCCESS

INSERT INTO Customers (CustomerID, Name, Email, PhoneNumber) 
VALUES (4, 'Eve Adams', NULL, '4445556666');  -- ERROR: Only one NULL allowed in 'Email'

Practical Use Cases

🔹 Primary Key Use Case: Used for ID fields like EmployeeID, OrderID, ensuring unique identification of each record.

🔹 Unique Key Use Case: Used for fields like Email, Username, or PhoneNumber, where values must be unique but are not the primary identifier.

SQL Performance Considerations

• Primary keys use Clustered Indexes, improving retrieval speed but impacting performance when frequently updated.
• Unique keys use Non-Clustered Indexes, optimizing lookup efficiency for specific columns.

Conclusion

Understanding the differences between Primary Keys and Unique Keys is critical for designing scalable and efficient databases. While both constraints enforce uniqueness, Primary Keys are used for uniquely identifying rows, while Unique Keys ensure data integrity without being the primary row identifier.

By implementing these constraints effectively, you can improve data consistency and optimize query performance in SQL databases. 🚀

🔗 Further Reading: Learn more about SQL constraints on Oracle’s official documentation.