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).
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.
Why Use UNIQUE?
- Prevent duplicate entries (email, phone, username)
- Enforce business rules
- Automatically creates an index
- Improves data integrity
Basic Syntax
Simple Example
Valid Inserts
Invalid Insert (Duplicate)
- Error: Duplicate entry for UNIQUE key
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
Adding UNIQUE Using ALTER TABLE
Dropping UNIQUE Constraint
- UNIQUE is enforced via an index in MySQL.
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 |
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
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?
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
