Primary Key vs Unique Key in SQL

💡 Concept Name

Primary Key and Unique Key – Both are constraints to enforce uniqueness of data in SQL tables but differ in purpose and behavior.

📘 Quick Intro

A Primary Key uniquely identifies each row in a table and does not allow NULLs. A Unique Key also enforces uniqueness but can accept NULL values (depending on the DBMS). A table can have only one Primary Key but multiple Unique Keys.

🧠 Analogy / Short Story

Think of the Primary Key like a national ID number that uniquely identifies a citizen—no two citizens can have the same ID, and every citizen must have one. Unique Keys are like email addresses: they must be unique but some people may not have one yet (NULL allowed). Primary Keys are mandatory and central to identity, Unique Keys add extra uniqueness for other attributes.

🔧 Technical Explanation

🔑 Primary Key is a column or set of columns that uniquely identifies each record.
❌ Primary Key columns cannot contain NULL values.
✅ Each table can have only one Primary Key.
🔑 Unique Key also enforces uniqueness but allows NULLs (implementation-dependent).
✅ A table can have multiple Unique Keys.
🔄 Both create unique indexes internally to speed up lookups.

🎯 Purpose & Use Case

✅ Use Primary Key as the main row identifier in relational tables.
✅ Use Unique Key to enforce uniqueness on columns like email, username, or serial numbers.
✅ Primary Key is essential for relationships and foreign key references.
✅ Unique Key is useful for data validation without being the main identifier.

💻 Real Code Example

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

-- Unique Key example allowing NULLs
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) UNIQUE,
    Phone VARCHAR(15) UNIQUE NULL
);

❓ Interview Q&A

Q1: Can a table have multiple primary keys?
A: No, a table can have only one Primary Key but it can be composite (multiple columns).

Q2: Can primary key columns contain NULL?
A: No, Primary Key columns cannot be NULL.

Q3: Can a Unique Key have multiple NULL values?
A: Yes, depending on DBMS, Unique Key columns can have multiple NULLs.

Q4: Are indexes created automatically for keys?
A: Yes, both Primary Key and Unique Key create unique indexes automatically.

Q5: Which key is used to create foreign key relationships?
A: Primary Key is referenced by foreign keys.

Q6: Can a Unique Key be composite?
A: Yes, Unique Keys can also be composite keys.

Q7: What happens if you insert duplicate values in a unique key column?
A: The database throws a constraint violation error.

Q8: Is it mandatory to define a primary key in a table?
A: While recommended, it's not mandatory in some DBMS.

Q9: Can primary key values be updated?
A: Generally not recommended as it may break relationships.

Q10: What is the main benefit of using keys?
A: They ensure data integrity by preventing duplicates and enabling efficient lookup.

📝 MCQs

Q1. Can a table have multiple primary keys?

Yes
No
Only in MySQL
Only with constraints

Q2. Can primary key columns contain NULL?

Yes
No
Sometimes
Depends on DBMS

Q3. Can unique key columns have NULL values?

Yes
No
Only one NULL
Depends on DBMS

Q4. Which key is referenced by foreign keys?

Unique Key
Primary Key
Neither
Both

Q5. How many unique keys can a table have?

Only one
Multiple
None
Two

Q6. Can unique keys be composite?

No
Yes
Only in SQL Server
Only in PostgreSQL

Q7. Does primary key create an index automatically?

No
Yes
Depends on DBMS
Only if specified

Q8. What happens on duplicate unique key insert?

Overwrite
Ignore
Error
Insert anyway

Q9. Is primary key mandatory?

Always
Not always
Only for foreign keys
Only for indexed tables

Q10. What is the main purpose of keys?

Speed up queries
Ensure uniqueness and integrity
Enable deletes
Allow NULLs

💡 Bonus Insight

Use Primary Keys for the main identity of rows and Unique Keys to enforce additional uniqueness rules. Be mindful of NULL behavior in unique constraints across different database systems.

📄 PDF Download

Need a handy summary for your notes? Download this topic as a PDF!

📄 PDF Download Coming Soon