MySQL UNIQUE Constraint

🔐 MySQL UNIQUE Constraint

The UNIQUE constraint in MySQL ensures that all values in a column (or a set of columns) are unique.

👉 It prevents duplicate values while still allowing NULL (unlike PRIMARY KEY).

1️⃣ What is UNIQUE Constraint?

  • Ensures no duplicate values in a column

  • Multiple UNIQUE constraints allowed per table

  • NULL values are allowed (one or more, depending on engine/version)

📌 Used when values must be unique but not the table’s identity.

2️⃣ Why Use UNIQUE?

✔ Prevent duplicate entries (email, phone, username)
✔ Enforce business rules
✔ Automatically creates an index
✔ Improves data integrity

3️⃣ Basic Syntax



 

4️⃣ Simple Example



 

Valid Inserts ✅



 

Invalid Insert ❌ (Duplicate)

INSERT INTO users (email, name) VALUES ('a@x.com', 'Neha');

➡ Error: Duplicate entry for UNIQUE key

5️⃣ UNIQUE Constraint on Multiple Columns (Composite UNIQUE)

Ensures the combination is unique.



 

✔ Same student can enroll in different courses
❌ Same student–course pair cannot repeat

6️⃣ Adding UNIQUE Using ALTER TABLE



 

7️⃣ Dropping UNIQUE Constraint



 

📌 UNIQUE is enforced via an index in MySQL.

8️⃣ UNIQUE vs PRIMARY KEY ⭐ (Interview Favorite)

Feature UNIQUE PRIMARY KEY
Duplicate values ❌ No ❌ No
NULL allowed ✔ Yes ❌ No
Keys per table Multiple Only one
Auto index ✔ Yes ✔ Yes
Table identity ❌ No ✔ Yes

9️⃣ UNIQUE vs INDEX ❓

  • UNIQUE: Prevents duplicates + index

  • INDEX: Improves speed only (duplicates allowed)

🔟 Important Rules (Exam ⭐)

  • A table can have multiple UNIQUE constraints

  • UNIQUE allows NULL

  • UNIQUE automatically creates an index

  • Can be single-column or composite

1️⃣1️⃣ Common Mistakes ❌

❌ Expecting UNIQUE to block NULLs
❌ Confusing UNIQUE with PRIMARY KEY
❌ Forgetting to drop the correct index name
❌ Overusing UNIQUE where PRIMARY KEY fits better

📌 Interview Questions & MCQs (Very Important)

Q1. What does UNIQUE constraint do?

A) Prevent NULL
B) Prevent duplicates
C) Speed queries only
D) Create foreign key

✅ Answer: B

Q2. Can a table have multiple UNIQUE constraints?

A) Yes
B) No

✅ Answer: A

Q3. Does UNIQUE allow NULL?

A) Yes
B) No

✅ Answer: A

Q4. UNIQUE constraint automatically creates:

A) View
B) Trigger
C) Index
D) Procedure

✅ Answer: C

Q5. Which statement creates composite UNIQUE?

UNIQUE (col1, col2)

A) Correct
B) Incorrect

✅ Answer: A

Q6. Which is better for table identity?

A) UNIQUE
B) PRIMARY KEY

✅ Answer: B

🔥 Real-Life Use Cases

✔ Email ID
✔ Username
✔ Phone number
✔ Aadhaar / PAN (if applicable)
✔ Product codes

✅ Summary

  • UNIQUE ensures no duplicate values

  • Allows NULL

  • Multiple UNIQUE constraints per table

  • Automatically indexed

  • Different from PRIMARY KEY

  • Very important for SQL exams & interviews

You may also like...