🔑 Difference Between Primary Key and Unique Key in SQL

📘 Definition

In SQL, both Primary Key and Unique Key are used to enforce uniqueness of records in a table — but they serve slightly different purposes and have different rules.

🔍 1. Primary Key

• Uniquely identifies each row in a table.
• Only one primary key is allowed per table.
• Cannot have NULL values.
• Can be a single column or composite (multi-column).

✅ Example:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50)
);        

🧩 2. Unique Key

• Also ensures that all values are unique in a column or set of columns.
• A table can have multiple unique keys.
• Can have NULL values (but only one NULL per column in most RDBMS like MySQL).
• Used to enforce alternate unique constraints.

✅ Example:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);        

📊 Comparison Table

🧠 Real-World Analogy

• Primary Key: Like a Roll Number in school — one per student, mandatory, and unique.
• Unique Key: Like an email address — can also be unique, but not necessarily mandatory.