Difference Between Primary Key and Foreign Key
Primary key vs foreign key in SQL — unique row identity vs referential links, NULL rules and integrity — with examples and common database interview questions.
Expected Interview Answer
A primary key uniquely identifies each row in a table and cannot be NULL, while a foreign key is a column that references the primary key of another table to link the two and enforce referential integrity.
A table has exactly one primary key (which may span multiple columns), it must be unique and non-NULL, and it is automatically indexed. A foreign key lives in a child table, points to the primary key of a parent table, and may repeat or be NULL; it prevents orphan rows by rejecting values that don’t exist in the parent. Together they model relationships and keep data consistent across normalized tables.
- Primary key: guarantees unique row identity
- Foreign key: enforces valid references between tables
- Together: prevent orphan and inconsistent data
AI Mentor Explanation
A primary key is like a player’s unique jersey number on the roster — no two players share it, and it identifies each one exactly. A foreign key is like that number written on the scorecard for each delivery: it points back to the roster, and you can’t record a delivery for a jersey number that isn’t on the roster. The primary key gives identity; the foreign key links records to it and blocks invalid references.
Primary and foreign keys
Customers
- customer_id (PK)
- name
Orders
- order_id (PK)
- customer_id (FK)
Step-by-Step Explanation
Step 1
Primary key
One per table; unique and non-NULL; identifies each row; auto-indexed.
Step 2
Foreign key
In the child table; references a parent’s primary key.
Step 3
Referential integrity
FK values must exist in the parent — no orphan rows.
Step 4
Cardinality
FK values may repeat or be NULL; PK values cannot.
What Interviewer Expects
- Unique/non-NULL identity for primary keys
- Foreign key referencing a parent primary key
- Referential integrity / orphan prevention
- That FK can repeat/be NULL while PK cannot
Common Mistakes
- Saying a table can have multiple primary keys (it has one, possibly composite)
- Claiming a foreign key must be unique
- Confusing primary key with a unique key
- Forgetting the primary key cannot be NULL
Best Answer (HR Friendly)
“A primary key uniquely identifies each row in a table and can’t be empty. A foreign key is a column in one table that points to another table’s primary key, linking the two and making sure you can’t reference something that doesn’t exist.”
Code Example
CREATE TABLE customers (
customer_id INT PRIMARY KEY, -- unique, non-NULL
name VARCHAR(100)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);Follow-up Questions
- What is a composite primary key?
- What is the difference between a primary key and a unique key?
- What are ON DELETE CASCADE and ON DELETE SET NULL?
- Can a foreign key reference the same table (self-reference)?
MCQ Practice
1. Which statement about a primary key is true?
A primary key uniquely identifies rows, cannot be NULL, and there is one per table.
2. A foreign key ensures?
It restricts values to those existing in the referenced primary key, preventing orphans.
3. Which can contain NULL values?
A foreign key may be NULL (optional relationship); a primary key cannot.
Flash Cards
Primary key? — Uniquely identifies each row; unique, non-NULL, one per table.
Foreign key? — References another table’s primary key to link tables and enforce integrity.
Can a foreign key be NULL? — Yes — a primary key cannot.
What does a foreign key prevent? — Orphan rows — values with no matching parent record.