Primary & Foreign Keys — Choose primary keys and define foreign key relationships (conceptual) - SQL

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

1
Step 1 — Identify Entities and Attributes

We 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.
2
Step 2 — Determine Candidate Keys

For 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.
3
Step 3 — Select Primary Keys

We 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).
4
Step 4 — Define Foreign Key Relationships

Now 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.
5
Step 5 — Choose Referential Actions

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

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.

Strengths vs. limitations of primary and foreign key constraints

⚖️

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.

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.

Conceptual keys in this lesson vs. advanced database theory

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

Question 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?

Show answer

A primary key's purpose is to uniquely and definitively identify every row in a table. If a PK column allowed NULLs, a row would lack an identity—it would be impossible to reference it from a foreign key or distinguish it from other NULL-keyed rows. The entity integrity rule (Codd's Rule) formally forbids NULLs in any component of a primary key. A UNIQUE constraint enforces value uniqueness but treats NULL as "unknown," so most RDBMSs allow one NULL under UNIQUE. The PK, by contrast, combines UNIQUE and NOT NULL, ensuring every row is fully addressable.

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

Show answer

The ISBN is the strongest natural candidate key—it is globally unique by design, assigned once and never reused, and is already a standard identifier for books. Although it is a VARCHAR(13) rather than a compact integer, its stability and universal recognition make it a defensible primary key. An alternative approach would be to add a surrogate book_id SERIAL as the PK and place a UNIQUE constraint on isbn. This trades readability for a narrower, faster-joining integer key. The columns title and pub_year are not candidate keys because multiple books can share a title or publication year. The combination (title, author_id, pub_year) might be unique in practice but is fragile and overly wide for a PK.

Question 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?

Show answer

The friendships table requires two foreign keys: user_a_id → users.user_id and user_b_id → users.user_id. For the primary key, (user_a_id, user_b_id) serves as a composite PK, preventing duplicate rows for the same pair. To prevent the bidirectional duplication problem, enforce a CHECK constraint: CHECK (user_a_id < user_b_id). This canonical ordering ensures each pair is stored exactly once. Alternatively, store both directions and use a surrogate PK, but this doubles storage and complicates deletion. The CHECK-constrained composite key is more space-efficient and semantically cleaner for undirected friendship graphs.

Question 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?

Show answer

Core tables: patients(patient_id PK, name, dob), doctors(doctor_id PK, name, specialty), departments(dept_id PK, dept_name), appointments(appt_id PK, patient_id FK, doctor_id FK, dept_id FK, appt_date, notes), and patient_doctor_history(patient_id FK, doctor_id FK, start_date, end_date, PK(patient_id, doctor_id, start_date)). Referential actions: For appointments → patients, use ON DELETE RESTRICT (never delete a patient with medical history). For appointments → doctors, use ON DELETE SET NULL (if a doctor leaves, the appointment record survives with a NULL doctor_id for audit). For appointments → departments, use ON UPDATE CASCADE because department reorganizations should transparently update references. For patient_doctor_history FKs, use ON DELETE RESTRICT on both sides since historical records must be preserved for legal compliance.

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

Show answer

In a distributed database, data is split across shards (or ranges) based on the primary key's value. An auto-incrementing integer produces monotonically increasing values, which means all new inserts are directed to the shard responsible for the highest key range—this creates a "hot shard" bottleneck. UUIDs (especially v4, which are random) distribute inserts uniformly across shards, eliminating the hotspot. However, random UUIDs are 16 bytes (vs. 4–8 for integers), increasing index size and join cost. They also defeat range-scan locality: rows inserted close in time are physically scattered. A compromise is the "time-ordered UUID" (UUIDv7 or Snowflake IDs), which embeds a timestamp prefix for rough chronological ordering while still providing enough randomness to spread writes across shards. The designer must weigh write throughput (favoring random keys) against read patterns and index compactness (favoring sequential keys) when selecting a PK strategy for distributed systems.

SUMMARY

Lesson Summary

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.