What is a Primary Key vs Foreign Key?
Learn the difference between a primary key and a foreign key in SQL, how referential integrity works, and how to define both with real examples.
Expected Interview Answer
A primary key is a column, or set of columns, that uniquely identifies each row in a table and cannot contain NULLs, while a foreign key is a column in one table that references a primary key in another table to enforce a valid relationship between them.
A table can have only one primary key, and the database automatically enforces its uniqueness with a built-in index, guaranteeing no two rows share the same value. A foreign key, by contrast, does not need to be unique — many rows in the child table can reference the same parent row, which is exactly how a one-to-many relationship (e.g. one customer, many orders) is modeled. The database enforces referential integrity through the foreign key: it rejects inserting a child row that points to a non-existent parent, and depending on the ON DELETE/ON UPDATE rules, it can cascade, restrict, or nullify changes when the parent row is deleted or updated. Together, primary and foreign keys are the backbone of normalized relational schemas, letting related data live in separate tables without becoming orphaned or duplicated.
- Primary keys guarantee every row is uniquely and reliably identifiable
- Foreign keys enforce that relationships between tables stay valid
- Cascading rules automate consistent updates/deletes across related tables
- Together they enable normalized schemas without data duplication
AI Mentor Explanation
A primary key is like a player's unique jersey number on a team — no two players share it, and every player must have one. A foreign key is like each match scorecard referencing a jersey number to record who batted, always pointing back to a real, existing player rather than to someone made up.
How a primary key and foreign key connect two tables
customers (parent)
- id (PRIMARY KEY, unique)
- name
orders (child)
- id (PRIMARY KEY)
- customer_id (FOREIGN KEY -> customers.id)
- amount
Step-by-Step Explanation
Step 1
Designate the primary key
Choose a column, or combination of columns, that will uniquely identify every row and never be NULL.
Step 2
Enforce uniqueness
The database automatically builds a unique index behind the primary key constraint.
Step 3
Identify the relationship
Determine which table is the 'many' side that needs to reference the 'one' side.
Step 4
Add the foreign key column
Create a column in the child table that will hold the parent's primary key value.
Step 5
Declare the constraint
Use FOREIGN KEY ... REFERENCES to have the database enforce that only valid parent values can be inserted.
Step 6
Set cascade behavior
Decide ON DELETE/ON UPDATE rules (CASCADE, RESTRICT, SET NULL) for what happens when a parent row changes.
What Interviewer Expects
- Defines a primary key as a unique, non-null row identifier
- Defines a foreign key as a reference to another table's primary key
- Explains that foreign keys enforce referential integrity
- Knows a table has one primary key but can have multiple foreign keys
- Can describe cascade behavior on delete/update
Common Mistakes
- Thinking a foreign key must also be unique in its own table
- Forgetting that a primary key cannot contain NULL values
- Not declaring the foreign key constraint, leaving referential integrity unenforced
- Confusing a foreign key with just 'any column that happens to match another table'
Best Answer (HR Friendly)
“A primary key is a unique ID for each row in a table, like a customer's account number, and no two rows can share one. A foreign key is how another table points back to that ID, like an order recording which customer placed it, which keeps the data connected and accurate.”
Code Example
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
-- Works: customer 1 exists
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1, 250);
-- Fails: customer 99 does not exist
INSERT INTO orders VALUES (102, 99, 500);
-- ERROR: foreign key constraint violationFollow-up Questions
- Can a table have more than one foreign key?
- What is a composite primary key?
- What does ON DELETE CASCADE do?
- What is the difference between a primary key and a unique key?
- Can a foreign key reference a column that isn't a primary key?
MCQ Practice
1. How many primary keys can a single table have?
A table can have exactly one primary key, though it may consist of multiple columns (a composite key).
2. What does a foreign key enforce?
A foreign key enforces referential integrity, requiring its values to match an existing primary key value in the referenced table.
3. Can multiple rows in a child table reference the same primary key value in a parent table?
Foreign keys are not required to be unique, so many child rows can point to the same parent row, modeling a one-to-many relationship.
Flash Cards
What is a primary key? — A column or set of columns that uniquely identifies each row in a table and cannot be NULL.
What is a foreign key? — A column that references a primary key in another table, enforcing a valid relationship between the two.
Must a foreign key value be unique in its own table? — No — many rows can share the same foreign key value, which is how one-to-many relationships work.
What happens if you insert a foreign key value with no matching parent row? — The database rejects the insert, enforcing referential integrity.