What is the difference between a primary key, a unique key and a foreign key?
A primary key uniquely identifies each row and cannot be NULL; a table has only one. A unique key also prevents duplicates, but a table can have many and they usually allow NULL. A foreign key is a column that must match a primary or unique key in another table, which keeps related data consistent.
Explain it simply
Think of a college database. Every student gets a roll number. No two students share it, and nobody can be admitted without one. That is the primary key.
Students also have an email address. It must not repeat either, but it is not the main way the college identifies a student, and a new student might not have one yet. That is a unique key.
The marks table records which student scored what. Each mark row stores a roll number, and that roll number must belong to a real student. You cannot enter marks for roll number 999 if no such student exists. That is a foreign key.
Example
CREATE TABLE students (
roll_no int PRIMARY KEY, -- unique + NOT NULL, one per table
name varchar(100) NOT NULL,
email varchar(150) UNIQUE -- no duplicates, NULL allowed
);
CREATE TABLE marks (
roll_no int REFERENCES students (roll_no), -- foreign key
subject varchar(50),
score int,
PRIMARY KEY (roll_no, subject) -- composite primary key
);
INSERT INTO students VALUES (1, 'Anjali', 'anjali@college.edu');
INSERT INTO students VALUES (2, 'Rahul', NULL); -- OK, email can be NULL
INSERT INTO students VALUES (3, 'Neha', 'anjali@college.edu');
-- ERROR: duplicate key value violates unique constraint
INSERT INTO marks VALUES (1, 'DBMS', 88); -- OK
INSERT INTO marks VALUES (999, 'DBMS', 70);
-- ERROR: violates foreign key constraint (no student 999)How it works
| Primary key | Unique key | Foreign key | |
|---|---|---|---|
| Purpose | Identify each row | Prevent duplicate values | Link to another table |
| How many per table | One (can span several columns) | Many | Many |
| NULL allowed | No | Usually yes | Yes, unless declared NOT NULL |
| Duplicates allowed | No | No (for non-NULL values) | Yes, many marks rows can point to one student |
| Creates an index | Yes | Yes | Not automatically in PostgreSQL or SQL Server |
A foreign key also controls what happens when the parent row changes. By default the database refuses to delete a student who still has marks. You can choose ON DELETE CASCADE to delete the marks too, or ON DELETE SET NULL to clear the link.
NULL handling in unique keys varies a little. PostgreSQL and MySQL allow many NULLs in a unique column, because NULL is not equal to NULL. SQL Server allows only one NULL in a unique constraint.
Real-world use
- Primary key:
order_idin an orders table,pnrin an IRCTC-style bookings table. - Unique key: a user's phone number or email on a sign-up form, so the same person cannot register twice.
- Foreign key:
orders.customer_idpointing tocustomers.id, so no order can belong to a customer who does not exist.
Common mistakes
- Saying a table can have two primary keys. It has one, though that one key can use several columns (a composite key).
- Saying a unique key can never be NULL. It usually can.
- Forgetting to index foreign key columns. Joins and parent deletes become slow on large tables.
- Using
ON DELETE CASCADEeverywhere. Deleting one customer could quietly wipe out years of order history.
What to say in the interview
"A primary key uniquely identifies each row. It cannot be NULL and a table has only one, though it can be composite. A unique key also blocks duplicates, but a table can have several and they normally allow NULLs. A foreign key references the primary or unique key of another table and enforces referential integrity, so a child row cannot point to a parent that does not exist. With ON DELETE options I can control whether deleting a parent is blocked, cascades or sets the child to NULL."
Follow-up questions
- What is a composite key? What is a candidate key?
- What is the difference between a natural key and a surrogate key?
- What does ON DELETE CASCADE do, and when is it risky?