What are primary keys and foreign keys?

Question

Accepted Answer

A **primary key** uniquely identifies each row in a table; a **foreign key** references the primary key of another table, establishing a **relationship** between tables and enforcing **referential integrity** (you can't reference a row that doesn't exist).

## Primary key — unique row identifier

```sql
CREATE TABLE users (
    id    INT PRIMARY KEY,         -- uniquely identifies each user (unique + not null)
    name  VARCHAR(100),
    email VARCHAR(255) UNIQUE
);
-- often auto-generated: id SERIAL PRIMARY KEY (Postgres) / AUTO_INCREMENT (MySQL)
```

A primary key is **unique** and **not null** — it identifies each row. It's automatically indexed (fast lookups) and is what foreign keys reference.

## Foreign key — reference another table

```sql
CREATE TABLE orders (
    id      INT PRIMARY KEY,
    user_id INT,
    total   DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)   -- user_id must match a users.id
);
```

A foreign key (`user_id`) references another table's primary key (`users.id`), creating the relationship "an order belongs to a user." The database **enforces** this: you can't insert an order with a `user_id` that doesn't exist in `users`.

## Referential integrity (the key benefit)

```sql
INSERT INTO orders (user_id, total) VALUES (999, 50);   -- ❌ ERROR if user 999 doesn't exist
-- the foreign key PREVENTS orphaned records (orders referencing non-existent users)
```

**Referential integrity** means the database guarantees relationships are valid — you can't have an order referencing a non-existent user. This prevents data corruption from orphaned/invalid references.

## ON DELETE / ON UPDATE behavior

```sql
FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE         -- delete the user → delete their orders too
    -- ON DELETE SET NULL     -- set user_id to NULL
    -- ON DELETE RESTRICT     -- prevent deleting a user who has orders (default-ish)
```

`ON DELETE`/`ON UPDATE` define what happens to dependent rows when the referenced row is deleted or changed — `CASCADE` (propagate), `SET NULL`, or `RESTRICT` (prevent). Choosing correctly is important to avoid accidental data loss or orphaned rows.

## Why it matters

Primary keys and foreign keys are fundamental to relational database design — they're how the relational model establishes identity and relationships between tables, so understanding them is essential for designing and working with any relational database. **Primary keys** (uniquely identifying each row, auto-indexed) are the foundation of table identity and what relationships reference. **Foreign keys** establish the relationships between tables (orders belong to users, etc.) that define the relational structure of an application's data.

Critically, foreign keys enforce **referential integrity** — the database guarantees relationships are valid (you can't reference a non-existent row), which **prevents data corruption** from orphaned or invalid references, a major reliability benefit that maintains data consistency automatically.

Understanding the **`ON DELETE`/`ON UPDATE` behaviors** (`CASCADE`, `SET NULL`, `RESTRICT`) is important because they determine what happens to related data when a referenced row changes or is deleted — choosing correctly prevents accidental data loss (e.g. CASCADE deleting more than intended) or orphaned records, a consequential design decision.

Since relationships between tables are the essence of relational databases, and since primary/foreign keys (and referential integrity) are how those relationships are defined and protected, understanding them — keys for identity and relationships, referential integrity for data consistency, and the on-delete behaviors for managing related data — is core, foundational knowledge for database design and a common interview topic essential to working correctly with relational data.