Learn the difference between primary key and foreign key in SQL with visual diagrams, code examples, and practical detective-themed scenarios.
Jump into the SQLNoir case files and put these tips to work.
If you've ever stared at a database and wondered why some columns are marked as keys while others aren't, you're not alone. Primary keys and foreign keys are the backbone of relational databases, but most explanations make them sound more complicated than they need to be. Let's fix that.
“PLEASE explain foreign keys to me like I am six years old”
Here's the TL;DR:
| Feature | Primary Key | Foreign Key |
|---|---|---|
| Purpose | Uniquely identifies each row | Links to another table |
| Uniqueness | Must be unique | Can have duplicates |
| NULL allowed | No | Yes (usually) |
| Count per table | Only one | Multiple allowed |
| Automatically indexed | Yes | No (but recommended) |
Quick reference comparison
A primary key is a column (or combination of columns) that uniquely identifies each row in a table. Think of it like a suspect ID in a criminal database: no two suspects share the same ID, and every suspect must have one.
Four rules of primary keys:
CREATE TABLE suspects (suspect_id INT PRIMARY KEY,name VARCHAR(100),
age INT,
last_seen DATE
);CREATE TABLE with primary key
You can also define a composite primary key using multiple columns. This is common in junction tables:
CREATE TABLE case_suspects ( case_id INT, suspect_id INT, role VARCHAR(50), PRIMARY KEY (case_id, suspect_id) );
The combination of case_id + suspect_id must be unique.
A foreign key is a column that references the primary key of another table. It creates a relationship between tables and enforces referential integrity (meaning you can't link to something that doesn't exist).
“Think of it like social security numbers. Your SSN is your primary key. When a bank stores your account, they use your SSN as a foreign key to link back to you.”
In a detective database, evidence belongs to a specific case. The case_id in the evidence table is a foreign key that references the case_id primary key in the cases table:
CREATE TABLE evidence (evidence_id INT PRIMARY KEY,case_id INT,
description TEXT,
FOREIGN KEY (case_id) REFERENCES cases(case_id)CREATE TABLE with foreign key
Unlike primary keys, foreign keys can have duplicates (many pieces of evidence can belong to the same case) and can be NULL (evidence might not be assigned to a case yet).
Want to practice JOINing tables with primary and foreign keys? SQLNoir's detective cases challenge you to query across multiple related tables to crack mysteries.
Keys create relationships between tables. In a detective database, you might have suspects, cases, evidence, and interviews. Here's how they connect:
Detective database schema showing primary and foreign key relationships
Here's the complete schema in SQL:
-- Suspects table (primary key: suspect_id) CREATE TABLE suspects ( suspect_id INT PRIMARY KEY, name VARCHAR(100), age INT, last_seen DATE ); -- Cases table (primary key: case_id) CREATE TABLE cases ( case_id INT PRIMARY KEY, title VARCHAR(200), status VARCHAR(50), lead_detective VARCHAR(100) ); -- Evidence table (foreign key references cases) CREATE TABLE evidence ( evidence_id INT PRIMARY KEY, case_id INT, description TEXT, found_date DATE, FOREIGN KEY (case_id) REFERENCES cases(case_id) ); -- Interviews table (foreign keys reference both cases AND suspects) CREATE TABLE interviews ( interview_id INT PRIMARY KEY, case_id INT, suspect_id INT, transcript TEXT, FOREIGN KEY (case_id) REFERENCES cases(case_id), FOREIGN KEY (suspect_id) REFERENCES suspects(suspect_id) );
Here's where it gets interesting. What happens when you delete a case that has linked evidence? The foreign key constraint controls this behavior:
What happens when you delete a parent row depends on your ON DELETE setting
Here's how you define these behaviors:
CREATE TABLE evidence (
evidence_id INT PRIMARY KEY,
case_id INT,
description TEXT,
FOREIGN KEY (case_id) REFERENCES cases(case_id)
ON DELETE CASCADE
);
-- Now when you delete a case:
DELETE FROM cases WHERE case_id = 1;
-- All evidence linked to case 1 is automatically deleted!CREATE TABLE evidence (
evidence_id INT PRIMARY KEY,
case_id INT,
description TEXT,
FOREIGN KEY (case_id) REFERENCES cases(case_id)
ON DELETE RESTRICT
);
-- Now when you try to delete a case with evidence:
DELETE FROM cases WHERE case_id = 1;
-- ERROR: Cannot delete - dependent records exist!cases: [(1, 'Miami Murder')] evidence: [(1, 1, 'Fingerprint'), (2, 1, 'Weapon')]
•Case 1 exists with 2 pieces of evidence linked to it
cases: (empty) evidence: (empty)
•Case 1 deleted
•Both evidence rows automatically removed
•No orphaned records
CASCADE deletes cascade to all related child rows
Here's the comprehensive side-by-side breakdown:
| Aspect | Primary Key | Foreign Key |
|---|---|---|
| Purpose | Uniquely identifies rows | Creates relationships between tables |
| Uniqueness | Must be unique (no duplicates) | Duplicates allowed |
| NULL values | Never allowed | Allowed (unless constrained) |
| Count per table | Exactly one | Zero or more |
| Automatically indexed | Yes (always) | No (must add manually) |
| References another table | No | Yes (references a primary key) |
| Can be composite | Yes (multiple columns) | Yes (multiple columns) |
| Modification | Difficult to change | Can be updated if new value exists |
| Delete behavior | Cannot delete if referenced | Configurable (CASCADE, RESTRICT, etc.) |
| Performance impact | Speeds up lookups | Slows writes (integrity checks) |
10-point comparison of primary and foreign keys
Put your primary and foreign key knowledge to work. Join suspects, interviews, and hotel check-ins to find the killer.
Start InvestigationJust because a column is named customer_id doesn't make it a foreign key. You need to explicitly define the constraint:
CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT -- No constraint! );
•Can insert orders with non-existent customer_id
•Orphaned records possible
•Data integrity compromised
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);•Database enforces valid customer_id
•Cannot insert invalid references
•Data integrity guaranteed
Always define foreign key constraints explicitly
Using email addresses, phone numbers, or social security numbers as primary keys seems convenient but causes problems when that data changes:
CREATE TABLE customers ( email VARCHAR(255) PRIMARY KEY, -- Bad idea! name VARCHAR(100) ); -- What happens when a customer changes their email? -- You have to update EVERY table that references it!
CREATE TABLE customers ( customer_id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255) UNIQUE, -- Unique constraint, not primary key name VARCHAR(100) ); -- Email can change; customer_id stays constant forever.
Primary keys are automatically indexed, but foreign keys are not. This means JOINs on foreign keys can be slow on large tables:
-- After creating your table with foreign key: CREATE INDEX idx_evidence_case_id ON evidence(case_id); CREATE INDEX idx_interviews_case_id ON interviews(case_id); CREATE INDEX idx_interviews_suspect_id ON interviews(suspect_id);
Index foreign key columns for faster JOINs and constraint checking.
Q1.A primary key column can contain NULL values.
Q2.How many foreign keys can a single table have?
Q3.What happens with ON DELETE CASCADE when you delete a parent row?
Q4.Why should you create an index on foreign key columns?
Yes! In junction tables for many-to-many relationships, the composite primary key often consists of two foreign keys. Example: a case_suspects table with (case_id, suspect_id) as the composite primary key, where both columns are also foreign keys.
No. A table can have only ONE primary key. However, that primary key can be a COMPOSITE key made of multiple columns. You might hear “multiple primary keys” but this is incorrect terminology.
A composite key is a primary key made of two or more columns. All columns together must be unique. Common in junction tables: PRIMARY KEY (order_id, product_id).
Foreign keys add overhead on INSERT, UPDATE, and DELETE because the database must check referential integrity. However, this overhead is usually small and the data integrity benefits outweigh the cost. Index your foreign key columns to minimize the impact.
No. Foreign keys are optional but strongly recommended. Without them, the database allows orphaned records (evidence linked to non-existent cases). You CAN skip them, but your data quality will suffer.
“I keep seeing these terms but nobody explains WHY you'd use them, just WHAT they are”
The WHY is simple: primary keys let you find specific rows instantly, and foreign keys let you connect related data without duplicating it. Without keys, you'd either have massive duplication or no way to link your data together.
Ready to practice with primary and foreign keys?
SQLNoir's detective cases let you query across related tables to solve mysteries. No signup required for beginner cases.
Start Your Investigation →