Primary & Foreign Keys — Choose primary keys and define...

SECTION 1

Historical Context & Motivation

Before the advent of the relational model, data was stored in flat files and hierarchical or network databases where relationships between records were encoded through physical pointers embedded in the storage layer itself. This tight coupling between logical meaning and physical representation made schema evolution painful and query formulation brittle. Edgar F. Codd's landmark 1970 paper, A Relational Model of Data for Large Shared Data Banks, proposed that data should be organized into relations (tables) and that integrity constraints—most notably primary and foreign keys—should be declared at the logical level, entirely independent of storage details.

The concepts of primary keys and foreign keys did not emerge in isolation. They grew from set theory and predicate logic, were refined through decades of academic debate, and were eventually codified in the SQL standard. Understanding their historical trajectory illuminates why modern relational databases enforce these constraints with such rigor—and why violating them produces the data anomalies that plague poorly designed systems.

1970

Codd's Relational Model

Edgar F. Codd publishes his seminal paper at IBM, introducing the concept of relations, candidate keys, and the principle that every tuple in a relation must be uniquely identifiable.

1974

System R & SEQUEL

IBM's System R prototype implements SEQUEL (later renamed SQL), providing the first practical syntax for declaring primary keys and referencing columns across tables, making Codd's theoretical constraints executable.

1986

SQL-86 (ANSI Standard)

The first ANSI/ISO SQL standard formalizes PRIMARY KEY and REFERENCES syntax, establishing a vendor-neutral language for declaring entity identity and referential integrity constraints.

1992

SQL-92 & Cascading Actions

SQL-92 extends foreign key support with ON DELETE CASCADE, ON UPDATE SET NULL, and other referential actions, giving designers fine-grained control over how deletions and updates propagate across related tables.

2003–Present

Modern Extensions

Subsequent SQL standards and RDBMS implementations introduce deferred constraint checking, partial indexes on keys, UUID-based primary keys, and composite foreign key support, adapting Codd's original vision to distributed and cloud-native architectures.

The central question these constraints address is deceptively simple: How do we guarantee that every row can be found unambiguously, and that references between tables always point to rows that actually exist? Primary keys solve the first half; foreign keys solve the second. Together, they form the backbone of referential integrity—the property that ensures a relational database never contains dangling pointers or orphaned records.

SECTION 2

Core Principles & Definitions

At the conceptual level, relational database design rests on a small set of well-defined constraints that govern how rows are identified and how tables reference one another. Before examining implementation details, it is essential to internalize the foundational ideas that make primary and foreign keys both necessary and sufficient for preserving data integrity across an entire schema.

Candidate Key

A candidate key is any minimal set of attributes whose values uniquely identify every tuple in a relation. "Minimal" means no proper subset of the key also guarantees uniqueness. A table may have multiple candidate keys.

Primary Key

The primary key is the candidate key chosen by the designer to serve as the official row identifier. It must be unique and NOT NULL for every row. A table has exactly one primary key, though it may be composite (multi-column).

Foreign Key

A foreign key is a set of attributes in one table (the referencing table) whose values must match the primary key (or a unique key) in another table (the referenced table). It encodes the logical relationship between entities.

Referential Integrity

The constraint that every non-NULL foreign key value must correspond to an existing primary key value in the referenced table. Violations—called dangling references—are prevented by the DBMS at insert, update, and delete time.

Surrogate vs. Natural Key

A natural key uses real-world attributes (e.g., SSN, ISBN). A surrogate key is a system-generated value (e.g., auto-increment integer, UUID) with no inherent business meaning. Each approach has trade-offs in stability, size, and readability.

✦ KEY TAKEAWAY

Think of a primary key as a student ID badge that uniquely identifies every person on campus—no two badges share the same number, and nobody is allowed on campus without one. A foreign key is like a course enrollment form that records a student ID: the registrar will reject any form bearing an ID number that does not correspond to an actual student. This simple mechanism guarantees that enrollments never reference ghost students, just as foreign keys guarantee that references never point to nonexistent rows.

Two additional properties merit attention. First, a primary key column (or set of columns) implicitly creates a unique clustered index in most RDBMSs, which accelerates lookups and joins. Second, foreign keys may allow NULLs—representing an optional relationship—unless the designer explicitly adds a NOT NULL constraint. Understanding these nuances prevents common schema-design mistakes that surface only under production workloads.

SECTION 3

Visual Explanation — Entity-Relationship Diagram

The most intuitive way to grasp primary and foreign key relationships is through a visual schema diagram. The following SVG depicts a simplified university database with four tables: students, courses, enrollments, and departments. Primary key columns are marked with a key icon, and foreign key arrows illustrate referential dependencies between tables.

The diagram shows four tables in a university database. Each table's primary key is indicated by a 🔑 symbol, while foreign keys are marked with 🔗. Dashed arrows point from the referencing table to the referenced table. Notice that enrollments uses a composite primary key (student_id, course_id), both of which are simultaneously foreign keys—a hallmark of associative (junction) tables.

Several design decisions are visible in this diagram. The departments table sits at the top of the dependency hierarchy: it is referenced by both students and courses, but it does not itself reference any other table. This makes it a parent (or referenced) table. The enrollments table, conversely, is a child (or referencing) table that resolves the many-to-many relationship between students and courses by referencing both parent tables through its composite foreign-and-primary key.

SECTION 4

How Keys Work — Constraint Enforcement & Relational Algebra

While primary and foreign keys are most often discussed at the SQL syntax level, they have precise foundations in relational algebra and set theory. Understanding the formal underpinnings clarifies why certain design choices prevent anomalies and why the DBMS rejects specific operations at runtime.

Uniqueness & Entity Integrity

ENTITY INTEGRITY RULE

∀ tᵢ, tⱼ ∈ R : i ≠ j ⟹ tᵢ[K] ≠ tⱼ[K] ∧ ∀ tₖ ∈ R : tₖ[K] ≠ NULL

For a relation R with primary key attribute(s) K: no two distinct tuples may share the same key value, and no component of K may be NULL. This guarantees that every tuple is individually addressable.

Referential Integrity

REFERENTIAL INTEGRITY CONSTRAINT

∀ t ∈ S : t[FK] ≠ NULL ⟹ ∃ u ∈ R : t[FK] = u[PK]

For referencing relation S with foreign key FK referencing relation R's primary key PK: every non-NULL foreign key value must match exactly one primary key value in R. This is the formal definition of referential integrity.

DBMS Enforcement Points

The DBMS enforces these constraints at four critical operation points. On INSERT into the child table, the engine verifies that the foreign key value exists in the parent. On DELETE from the parent table, it checks whether any child rows reference the row being deleted—and applies the configured referential action (RESTRICT, CASCADE, SET NULL, or SET DEFAULT). On UPDATE of a parent's primary key, the same referential actions apply. Finally, on UPDATE of a child's foreign key, the engine re-validates the reference against the parent table.

⚙️ Referential Actions Summary

RESTRICT / NO ACTION — block the parent deletion or update if child rows exist. CASCADE — propagate the delete or update to all matching child rows. SET NULL — set the child's foreign key to NULL. SET DEFAULT — set the child's foreign key to its column default value.

SECTION 5

Choosing Primary Keys — Natural vs. Surrogate

Selecting the right primary key is one of the most consequential decisions in schema design. The choice affects storage overhead, join performance, migration complexity, and even business logic. Designers must evaluate every candidate key against a set of criteria before promoting one to primary key status. The two dominant strategies—natural keys and surrogate keys—each carry distinct advantages and risks that depend on the application domain.

Side-by-side comparison of natural keys and surrogate keys. Green checkmarks (✅) denote advantages; red warnings (⚠) denote risks. In practice, many production schemas use a surrogate primary key while placing a UNIQUE constraint on the natural candidate to preserve business-level uniqueness.

Criteria for a Good Primary Key

Uniqueness — the value must be distinct across all current and future rows in the table.
Non-nullability — every row must have a value; NULLs are forbidden in primary key columns.
Stability (Immutability) — the value should rarely, if ever, change. Updates to a PK cascade to every child table, which is expensive and error-prone.
Simplicity — single-column keys are preferred over composite keys for join performance and readability.
Compactness — smaller data types (INT, BIGINT) produce smaller indexes and faster comparisons than VARCHAR or UUID.

In practice, many designers adopt a hybrid approach: a surrogate integer or UUID serves as the primary key for efficient joins and stable references, while a UNIQUE constraint on the natural candidate (e.g., email or isbn) preserves business-level uniqueness. This "belt and suspenders" pattern is widespread in enterprise applications and ORMs like Hibernate and Django.

SECTION 6

Worked Example — Designing Keys for an E-Commerce Schema

Consider a simplified e-commerce application with the following business rules: customers place orders, each order contains one or more line items, and each line item references a product from the catalog. We will walk through the process of identifying candidate keys, selecting primary keys, and defining foreign key relationships.

E-Commerce Key Design

Step 1 — Identify Entities and AttributesWe identify four entities: customers (customer_id, email, name, address), products (product_id, sku, title, price), orders (order_id, customer_id, order_date, status), and order_items (order_id, product_id, quantity, unit_price). The first task is to find candidate keys for each table.

Step 2 — Determine Candidate KeysFor customers, both customer_id (surrogate) and email (natural) are candidate keys—each uniquely identifies a customer. For products, product_id and sku are both candidates. For orders, only order_id qualifies (customer_id is not unique across orders). For order_items, the composite (order_id, product_id) is a candidate key, assuming a product appears at most once per order.

Candidate keys identified for all four tables.

Step 3 — Select Primary KeysWe select surrogate keys for customers and products because emails can change and SKUs may be reformatted. We add UNIQUE constraints on email and sku to enforce business uniqueness. For orders, order_id (auto-increment integer) is the primary key. For order_items, we choose the composite key (order_id, product_id).

PKs: customers(customer_id), products(product_id), orders(order_id), order_items(order_id, product_id).

Step 4 — Define Foreign Key RelationshipsNow we encode the relationships. orders.customer_id → customers.customer_id (each order belongs to one customer). order_items.order_id → orders.order_id (each line item belongs to one order). order_items.product_id → products.product_id (each line item references one product).

Three foreign key constraints defined, fully capturing the 1:N relationships.

Step 5 — Choose Referential ActionsFor orders.customer_id, we choose ON DELETE RESTRICT—deleting a customer with existing orders should be blocked (data retention requirements). For order_items.order_id, we choose ON DELETE CASCADE—if an order is canceled and deleted, all its line items should be removed automatically. For order_items.product_id, we choose ON DELETE RESTRICT—we should not delete a product that appears in historical orders.

Referential actions selected based on business rules: RESTRICT for data-preservation scenarios, CASCADE for dependent child data.

SECTION 7

Strengths, Limitations & Common Pitfalls

Primary and foreign keys are powerful tools, but they are not without trade-offs. Understanding both the strengths and the limitations of key-based integrity constraints helps designers make informed decisions, especially in high-throughput or distributed systems where enforcement costs are non-trivial.

Strengths vs. limitations of primary and foreign key constraints

Aspect	Strengths	Limitations / Pitfalls
Data Integrity	Guaranteed at the database level—application bugs cannot create orphan rows or duplicate primary keys.	Enforcement has a performance cost: every INSERT, UPDATE, and DELETE triggers constraint checks and index lookups.
Query Performance	Primary keys automatically create clustered indexes; foreign keys hint the optimizer about join selectivity.	Wide composite keys increase index size and slow joins. FK constraints on high-velocity tables can become bottlenecks.
Schema Clarity	Keys serve as executable documentation of relationships—any developer can read the DDL to understand the data model.	Over-reliance on implicit conventions (e.g., always naming FK columns table_id) can mask semantic nuances.
Distributed Systems	In single-node RDBMS, FK enforcement is transactionally safe and atomic.	In sharded or microservice architectures, cross-shard FK enforcement is expensive or impossible; integrity must be managed at the application level.
Migration & ETL	Keys prevent accidental data corruption during migrations.	Bulk data loads often require temporarily disabling FK checks for performance, risking integrity if re-enabled without validation.

⚖️ DESIGN HEURISTIC

Think of foreign key constraints as compile-time type checks for your data. Just as a statically typed language catches type mismatches before runtime, FK constraints catch referential violations before they corrupt query results. You could enforce them in application code, just as you could use a dynamically typed language—but the database-level guarantee is vastly more reliable because it applies universally to every client, script, and migration that touches the data.

SECTION 8

Connection to Advanced Theory — Normalization & Beyond

Primary and foreign keys are not isolated concepts—they are the foundation upon which database normalization is built. Normal forms (1NF through BCNF and beyond) rely on the identification of functional dependencies, and a functional dependency X → Y is precisely the statement that X could serve as a determinant (superkey) for attribute Y. Decomposing a relation into higher normal forms often involves creating new tables linked by foreign keys that reference the original primary key.

Conceptual keys in this lesson vs. advanced database theory

Concept	Introductory Level (This Lesson)	Advanced Theory
Key Selection	Choose a single stable attribute (or surrogate) as PK; apply UNIQUE to natural candidates.	Formal candidate key analysis via closure of functional dependencies; Armstrong's axioms for reasoning about key minimality.
Foreign Keys	Declare FK columns and referential actions (CASCADE, RESTRICT).	Inclusion dependencies, database schema graphs, and chase algorithms for testing lossless join decompositions.
Composite Keys	Used in junction tables to model many-to-many relationships.	Partial and transitive dependency analysis for 2NF and 3NF decomposition; multi-valued dependencies for 4NF.
Distributed Keys	Surrogate keys with auto-increment or UUID.	Snowflake IDs, CRDTs, vector clocks, and conflict-free replicated key generation in globally distributed databases.

As you progress into courses on database internals, distributed systems, or data warehousing, you will encounter scenarios where traditional FK enforcement is relaxed for performance—star schemas in OLAP systems often omit FK constraints, relying on ETL pipelines for integrity. Even in these contexts, the conceptual notion of primary and foreign keys remains indispensable for understanding data lineage, designing efficient join paths, and communicating schema intent to other engineers.

SECTION 9

Practice Problems

PROBLEM 1 — CONCEPTUAL

Explain why a primary key column cannot allow NULL values. How does this differ from a UNIQUE constraint, which does permit a single NULL in many RDBMS implementations?

PROBLEM 2 — BASIC APPLICATION

Given a table books(isbn VARCHAR(13), title VARCHAR, author_id INT, publisher_id INT, pub_year INT), identify all candidate keys and recommend a primary key. Justify your choice using the criteria discussed in this lesson (uniqueness, stability, simplicity, compactness).

PROBLEM 3 — INTERMEDIATE

A social media application has tables users(user_id, username) and friendships(user_a_id, user_b_id, created_at). Design the primary key and foreign keys for friendships. Address the issue that friendship is bidirectional—should (Alice, Bob) and (Bob, Alice) both exist, or should the schema prevent duplicates?

PROBLEM 4 — APPLIED

You are designing the database for a hospital system. Patient records must be linked to doctors, departments, and appointment histories. A patient may switch primary care doctors over time, and departments may be reorganized. Outline the tables, primary keys, and foreign keys. What referential actions would you choose for ON DELETE and ON UPDATE on each FK, and why?

PROBLEM 5 — CRITICAL THINKING

Some modern distributed databases (e.g., Google Spanner, CockroachDB) discourage or limit the use of auto-incrementing integer primary keys, instead recommending UUIDs or hash-based keys. Using your understanding of how primary keys interact with clustered indexes and data distribution, explain the technical reason for this recommendation and discuss the trade-offs involved.

SUMMARY

Lesson Summary

A primary key is the designated candidate key chosen to uniquely identify every row in a table. It must satisfy uniqueness and non-nullability, and it should ideally be stable, simple, and compact. Designers choose between natural keys (business-meaningful attributes) and surrogate keys (system-generated identifiers) based on the domain's stability and performance requirements.

A foreign key is a column (or set of columns) in a referencing table whose values must match the primary key of a referenced table, enforcing referential integrity. The DBMS provides referential actions (CASCADE, RESTRICT, SET NULL, SET DEFAULT) that govern what happens to child rows when a parent row is deleted or its key is updated. Together, primary and foreign keys form the backbone of relational database design, preventing orphan rows and dangling references, and they serve as the foundation for normalization and advanced schema analysis.