✏️ Explanatory Question
Level: Basic — A subtle question; both enforce uniqueness, so interviewers check if you know how they differ.
Both a Primary Key and a Unique Key guarantee that values in a column are unique. The differences come down to NULL handling, how many you can have, and indexing behaviour.
A Primary Key uniquely identifies each row, cannot contain NULL, and there can be only one per table. A Unique Key also enforces uniqueness but allows one NULL value, and a table can have many of them.
| Feature | Primary Key | Unique Key |
|---|---|---|
| Uniqueness | Enforced | Enforced |
| NULL values | Not allowed | Allowed (one NULL) |
| Number per table | Only one | Multiple allowed |
| Default index | Clustered index | Non-clustered index |
| Main purpose | Identify each row uniquely | Prevent duplicate values |
| Auto-created on | Created explicitly | Created explicitly |
CREATE TABLE customers (
customer_id INT PRIMARY KEY, -- one per table, NOT NULL
email VARCHAR(100) UNIQUE, -- allows one NULL, many can exist
phone VARCHAR(15) UNIQUE -- another unique key in same table
);
-- Works: email can be NULL
INSERT INTO customers (customer_id, email, phone)
VALUES (1, NULL, '9876543210');
-- Fails: primary key cannot be NULL
-- INSERT INTO customers (customer_id, email) VALUES (NULL, 'a@x.com');