Primary Key vs Foreign Key: The Complete Visual Guide (2026)

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.

Quick Answer: Primary Key vs Foreign Key

Here's the TL;DR:

• Primary key: Unique identifier for each row in a table (like a suspect ID in a criminal database)
• Foreign key: A reference to a primary key in another table (like case_id in an evidence table linking back to cases)
• Key difference: Primary keys ensure uniqueness; foreign keys ensure relationships

What is a Primary Key?

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:

1. Unique: No duplicates allowed
2. Not null: Every row must have a value
3. Immutable: Should rarely change (changing IDs causes chaos)
4. Indexed: Automatically indexed for fast lookups

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)
);

What is a Foreign Key?

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).

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:

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.

How Primary and Foreign Keys Work Together

Keys create relationships between tables. In a detective database, you might have suspects, cases, evidence, and interviews. Here's how they connect:

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)
);

Referential Integrity: What Happens When You Delete Data?

Here's where it gets interesting. What happens when you delete a case that has linked evidence? The foreign key constraint controls this behavior:

• CASCADE: Delete the parent, and all child rows are automatically deleted too
• RESTRICT: Prevent deletion if child rows exist (default in most databases)
• SET NULL: Set the foreign key to NULL when the parent is deleted
• NO ACTION: Similar to RESTRICT, but checked at end of transaction

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')]

cases: (empty)
evidence: (empty)

Primary Key vs Foreign Key: Full Comparison

Here's the comprehensive side-by-side breakdown:

Common Mistakes (And How to Avoid Them)

Mistake 1: Forgetting the Foreign Key Constraint

Just 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!
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) 
    REFERENCES customers(customer_id)
);

Mistake 2: Using Business Data as Primary Key

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.

Mistake 3: Not Indexing Foreign Key Columns

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);

Test Your Understanding

FAQ

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.