The Lock and the Key: Primary, Foreign, and Candidate Keys

Opening Scene

A library assigns every locker a unique number, stamped on a small brass plate. The librarian keeps a ledger: locker 142 belongs to a particular regular reader, locker 143 to another. When a reader returns a borrowed book, they hand over a claim ticket stamped with their locker number — that ticket is how the front desk knows exactly which locker the book belongs back in, even if two readers share the same first name.

In Plain English

A key, in data modelling, is simply whatever uniquely identifies a record so it never gets confused with another. A primary key is the one official identifier a table commits to using. A candidate key is any other field or combination of fields that could have served that role just as well. A foreign key is a reference, stored in one place, pointing back to the unique identifier of a record somewhere else — like a claim ticket pointing back to a specific locker.

The Old Way

Traditionally, choosing and managing keys is a careful, deliberate exercise:

The primary key is the locker number itself — the one identifier the library officially relies on. It must be unique to one locker and never reassigned to another, even after that locker’s reader moves away.
Candidate keys are other details that could have uniquely identified the locker — perhaps a reader’s library card number could have worked just as well. A table can have multiple candidate keys, but only one becomes the actual primary key.
Foreign keys are claim tickets — a reference stored in a different record (a borrowed-book entry) pointing back to a specific locker number. This is how two separate tables stay correctly linked.

Traditionally, modellers and database administrators manually decide which field becomes the primary key, write rules to enforce uniqueness, and manually trace down “orphaned” foreign keys — claim tickets pointing to lockers that no longer exist — whenever data quality problems arose.

What’s Changing (and Why AI Is the Reason)

AI-powered profiling tools now detect candidate keys automatically. Instead of a modeller manually testing which column or combination of columns is actually unique across millions of rows, profiling tools can scan the data and surface strong candidate keys in seconds — a task that used to require slow, manual querying.
AI is making broken references easier to catch before they cause damage. Orphaned foreign keys — claim tickets for lockers that don’t exist — used to surface only when a report broke or a join silently dropped rows. AI-assisted data quality tools now flag these mismatches proactively, often before anyone downstream notices.
AI is changing how confidently teams trust generated keys. As more pipelines auto-generate identifiers (for instance, when merging data from multiple AI-ingested sources), there’s new pressure to verify that an auto-generated key is genuinely stable and unique over time, not just unique in the sample it was built from — making key validation a recurring task, not a one-time setup step.

The Metaphor, Fully Extended

Library Locker Element	Data Modelling Concept
The locker’s brass number plate	The primary key
A reader’s library card number, which could have worked instead	A candidate key
The official ledger linking locker numbers to readers	The table enforcing primary key uniqueness
A claim ticket stamped with a locker number	A foreign key
The borrowed-book log referencing a locker number	The table holding the foreign key
A claim ticket pointing to a locker that’s been reassigned or removed	An orphaned foreign key
The librarian manually checking the ledger for duplicates	Manual uniqueness validation
A scanner instantly flagging a mismatched claim ticket	AI-assisted referential integrity checking
Choosing which detail becomes the “official” locker number	Selecting a primary key among candidate keys

For Beginners: What to Actually Do

For any new entity, list every field or combination of fields that could uniquely identify a record — that’s your candidate key list — before picking one as primary.
Avoid choosing a primary key that could plausibly change over time (like a phone number); prefer something stable and meaningless on its own (like an assigned ID).
Practice tracing a foreign key by hand at least once — follow a “claim ticket” back to its locker manually — before relying on tools to do it, so the concept sticks.
When using an AI profiling tool to suggest keys, double-check its suggestion against a sample of real records, not just trust the suggestion outright.

For Practitioners and Leaders: The Deeper Layer

Be cautious with “natural keys” (real-world values like email addresses) versus “surrogate keys” (artificially assigned IDs) — natural keys can carry hidden business meaning that changes unexpectedly, breaking assumptions downstream.
Referential integrity constraints are a design decision, not just a database feature — deciding whether to enforce them strictly, loosely, or not at all (common in some high-throughput or AI-ingested pipelines) has real consequences for data trustworthiness.
When AI tools suggest candidate keys from sampled data, remember sample-based uniqueness isn’t guaranteed at full scale — validate against the full dataset or a much larger sample before committing.
Track key stability over the life of a system; a key that was reliably unique at launch can quietly stop being so as new data sources or business rules get added — schedule periodic re-validation, especially in fast-evolving, AI-augmented pipelines.

Quick Recap

A primary key uniquely identifies a record; a candidate key is any field that could have served that role; a foreign key references another table’s primary key.
Choosing a stable, meaningless identifier as a primary key avoids future headaches.
Broken foreign key references (“orphans”) quietly cause inaccurate reports if left unchecked.
AI-powered profiling tools can now detect candidate keys and broken references automatically.
Auto-generated and AI-ingested keys still need periodic human validation, since uniqueness in a sample doesn’t guarantee uniqueness at scale.

Where This Fits in the Series

Article 2 covered the entities, attributes, and relationships that make up a model’s logical layer. This article zoomed into how individual records get uniquely identified and linked using keys. Next, Article 4 looks at how those same entities should be organized to avoid duplication and inconsistency, through the lens of tidying a kitchen pantry — the theory of normalization.

Image Instructions

Image 1 — Header banner (~1600×600px, wide format): A wide illustration of a library locker room, split left-to-right. On the left (“old way”), a wall of worn wooden lockers with hand-stamped brass number plates, beside a librarian’s desk holding a thick paper ledger — muted gray/blue tones throughout. On the right (“new way”), the same locker wall transitions into sleek, glowing digital lockers with floating teal-lit claim tickets visibly tracing back to their matching locker through soft light-trails. Bitt the Beaver stands near the desk holding a small key in one paw. Flat vector illustration style, clean lines, minimal in-image text.

Image 2 — Supporting diagram (~1200×800px): Placed directly after “The Metaphor, Fully Extended” table. A simplified, abstract infographic showing two boxes labeled “Table A” and “Table B” side by side. Table A has a small key icon next to one row (the primary key), and a dotted line travels from a row in Table B (labeled with a small ticket icon, representing the foreign key) across to that same row in Table A. The boxes and dotted line are rendered in muted gray/blue, except the connecting line glows in electric teal where it correctly links, and one broken, non-glowing dotted line trails off to nothing, representing an orphaned reference, with a small caution icon beside it. Bitt the Beaver appears small in the corner pointing at the broken line. Flat vector illustration, clean lines, minimal text, soft glow reserved only for the teal AI element.

Cross-series visual identity note: In every image across this series, muted gray/blue always represents “the old/traditional way” and electric teal/blue glow always represents “AI/the new layer.” Bitt the Beaver, the series’ recurring mascot, appears in every header banner and most supporting diagrams, with its prop or pose adapted to each article’s metaphor while its core design stays recognizable. Style is flat vector illustration with clean lines, minimal in-image text, and soft glow/gradient reserved exclusively for AI/new elements.