SQL Primary Key vs Unique Key - Understanding Uniqueness in Tables

Introduction

Every student in a school is unique — but not in the same way. Roll numbers are unique and mandatory. Aadhaar numbers are also unique but may not be available at admission time. In database terms, these differences are captured using Primary Keys and Unique Keys.

What is a Primary Key?

A Primary Key uniquely identifies each record in a table. It has two strict rules:

• It must be unique across all rows
• It cannot be NULL

What is a Unique Key?

A Unique Key also enforces uniqueness, but:

• It can accept NULL values
• You can have multiple unique keys in a table

Example – students Table

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  aadhaar_number BIGINT UNIQUE,
  email VARCHAR(100) UNIQUE
);

Explanation:

• roll_no is the Primary Key — required and must be unique
• aadhaar_number and email are Unique Keys — can be NULL if not provided

Insert Examples

-- Valid
INSERT INTO students VALUES (1, 'Aarav Sharma', 123456789012, 'aarav@example.com');

-- Valid even if Aadhaar is missing
INSERT INTO students VALUES (2, 'Diya Iyer', NULL, 'diya@example.com');

-- Invalid: duplicate roll_no (Primary Key violation)
INSERT INTO students VALUES (1, 'Mehul Agarwal', 222233334444, 'mehul@example.com');

-- Invalid: duplicate email (Unique Key violation)
INSERT INTO students VALUES (3, 'Sneha Patil', NULL, 'aarav@example.com');

Key Differences

Aspect	Primary Key	Unique Key
Uniqueness	Enforced	Enforced
NULL allowed?	No	Yes (once or more)
Count per table	Only one	Multiple allowed
Default index	Clustered index (in some DBs)	Non-clustered index
Use case	Main identifier	Alternative identifiers (like Aadhaar, email)

Real-World School Use Cases

• roll_no – Primary Key (must exist for every student)
• aadhaar_number – Unique Key (may be missing initially)
• email – Unique Key (optional but must not repeat)

Best Practices

• Always define a Primary Key for every table
• Use Unique Keys for business-critical identifiers (like email, phone)
• Do not overload Primary Key with business logic (keep it technical)
• Avoid NULLs in Unique Keys unless truly optional

Summary

Primary Key is your table’s identity — strict, non-negotiable, and singular. Unique Keys are like extra ID cards — optional but must be individually unique. Together, they shape a table’s reliability and help enforce real-world rules in digital data.

What’s Next?

Next: Foreign Key Constraints — how to build relationships across tables and enforce data consistency.