Primary Key vs Foreign Key Explained with Examples

If tables are the building blocks of a relational database, then Primary Keys and Foreign Keys are the connectors that transform isolated tables into a coherent, queryable system. Without them, you cannot enforce data integrity, prevent duplication, or run meaningful cross-table queries.

The Problem They Solve: Redundancy and Inconsistency

Imagine a Family table that stores each person's birth country. When five members share “United States of America”, it’s stored five times. This creates:

• Storage redundancy: the same long string repeated unnecessarily
• Inconsistency risk: typos like “Unted States” creep in without detection
• Update complexity: renaming a country means updating every single row

The solution: extract the repeated data into a lookup table and use keys to link the two tables together.

Step-by-Step: How Primary and Foreign Keys Work

Step 1: Create a Separate Location Reference Table

First, extract the repeating country data into its own dedicated table with a unique identifier for each entry:

Step 2: Update Family Table to Use the Code (Foreign Key)

Now replace the full country name in the Family table with a short code that references the Location table:

This is a pattern central to reference data management.

What Is a Primary Key?

A Primary Key uniquely identifies each row in a table. It is the single source of truth for that record’s identity. The rules are strict:

• No nulls: every row must have a value
• Must be unique: no two rows can share the same primary key
• Should be stable: once assigned, it should not change

Common patterns include auto-incrementing integers, UUIDs, and natural business keys (such as ISO country codes).

What Is a Foreign Key?

A Foreign Key is a column in one table that references the Primary Key of another table. It enforces referential integrity, ensuring that every reference points to a valid, existing record. If the Location table has no entry with ID 999, then no row in the Family table can use 999 as its Location_ID.

Running a SQL JOIN Using Keys

With both keys in place, you can combine data from multiple tables using a SQL JOIN:

SELECT f.Name, f.Relationship, b.CountryName
FROM Family f
JOIN Birth_Location b ON f.Location_ID = b.ID;

This query produces the following result:

Primary Key vs Foreign Key: Quick Reference

Why This Matters Beyond Simple Queries

Primary keys and foreign keys are not just SQL plumbing. They underpin critical enterprise data capabilities:

• Data normalisation: eliminating redundancy through structured table relationships
• Data integrity enforcement: preventing orphaned records and invalid references
• Master Data Management (MDM): maintaining golden records that other systems reference
• Data lineage and governance: tracing how data flows between tables and systems
• Data architecture: building scalable warehouse and lakehouse designs on solid relational foundations