What is the difference between a unique index and a primary key?
Both enforce uniqueness and create an index. A primary key also forbids NULL, there is only one per table, and it identifies each row. A table can have many unique indexes, and they usually allow NULLs.
Explain it simply
A roll number is the official identity of a student: everyone has exactly one and it can never be blank. A mobile number must be unique too, but one student may not have given any, and a student could have two numbers on file. The roll number is the primary key, the mobile number a unique index.
Example
CREATE TABLE students (
roll_no INT PRIMARY KEY,
email VARCHAR(100),
mobile VARCHAR(15)
);
CREATE UNIQUE INDEX uq_email ON students(email);
CREATE UNIQUE INDEX uq_mobile ON students(mobile);| Property | Primary key | Unique index |
|---|---|---|
| Count per table | One | Many |
| NULL allowed | No | Yes in MySQL and PostgreSQL, multiple NULLs; SQL Server allows one |
| Purpose | Row identity, target of foreign keys | Prevent duplicates in a column |
Foreign keys normally reference the primary key, though they can reference a unique key.
In InnoDB (MySQL), the primary key is also the clustered index, so row data is stored in its order.
What to say in the interview
"A primary key is unique and not null, one per table, and defines row identity. A unique index only prevents duplicates and can be created many times. Both are backed by an index."
Common mistakes
- Believing a unique column can never hold NULL.