Primary Keys & Foreign Keys

How a database uniquely identifies one specific row, and how rows in different tables point to each other.

What is it?

If a table has two customers both named "Sam Lee," how does the database tell them apart? It needs some column whose value is guaranteed to be unique for every single row — no two rows ever share it. That column is the table's primary key, most commonly an auto-generated id number.

Once every row can be uniquely identified, tables can reference each other. Say an orders table needs to record which user placed each order. Instead of copying that user's whole name and email into every order row, the orders table just stores the user's id in a column like user_id. That column is called a foreign key — it "points to" a primary key in another table, linking the two rows together without duplicating data.

Explain like I'm 10

A primary key is like a person's unique student ID number — no two students share one, even if their names are identical. A foreign key is like writing that student ID on a library book's checkout card instead of writing out the student's full name and address every time — it just points back to the one record that already has all the details.

Examples

A primary key

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT
);

Marking id as the PRIMARY KEY tells the database this column uniquely identifies each row, and it will enforce that no two rows share the same id.

A foreign key linking two tables

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total_cents INTEGER
);

-- orders table:
-- id | user_id | total_cents
-- 1  | 2       | 4599
-- 2  | 2       | 1200
-- 3  | 3       | 800

Each order row stores just the id of the user who placed it. Orders 1 and 2 both belong to the user with id 2, without repeating that user's name or email in every row.

How it works

A primary key is enforced by the database itself: it refuses to let you insert (or update) a row that would create a duplicate value in that column. Most tables use a simple auto-incrementing integer id for this, since it's guaranteed unique and never changes.

A foreign key is just a regular column whose values are expected to match a primary key value in another table. Many databases can enforce this too — refusing to insert an order with a user_id that doesn't actually exist in the users table — which keeps the two tables consistent with each other.

Why does it exist?

Without a reliable way to uniquely identify a row, you couldn't safely update or delete "this one specific record" — you'd risk accidentally matching several rows that look similar. And without foreign keys, you'd be forced to copy full details (name, email, address) into every related table, which wastes space and creates a mess the moment any of that copied data changes.

When to use it

Give every table a primary key, essentially always — it's the foundation for updating, deleting, and linking specific rows. Use a foreign key any time one table's rows naturally "belong to" or reference a row in another table, like an order belonging to a user.

When not to use it

There's rarely a reason to skip a primary key on a real table. Foreign keys can be left out for small, throwaway, or purely denormalized datasets where you've deliberately decided the tables don't need to stay in sync with each other.

Common mistakes

  • Using a naturally occurring value like an email address as the primary key, then having it break when a user changes their email.

  • Forgetting to add a foreign key column, and instead duplicating a related row's full details everywhere it's needed.

  • Assuming a foreign key column automatically keeps data in sync on its own — it only links rows; your queries still have to join them together to see combined data.

Practice exercises

  1. Easy:

    Explain why an auto-incrementing id is usually a better primary key choice than a person's name.

  2. Medium:

    Design a 'comments' table that belongs to both a 'posts' table and a 'users' table (who wrote the comment, and on which post). Name the foreign key columns you'd add.

  3. Hard:

    Describe what could go wrong in your database if you inserted a comment with a user_id that doesn't correspond to any real user.

Interview questions

What is a primary key?

A column (or set of columns) whose value uniquely identifies each row in a table, enforced by the database to never have duplicates.

What is a foreign key?

A column in one table that stores the primary key value of a row in another table, creating a link between the two without duplicating data.

Why use a generated id instead of a real-world value like an email as a primary key?

Real-world values can change over time (like an email being updated), which breaks anything referencing it; a generated id never changes.

What is a composite primary key?

A primary key made of more than one column together, where the combination of values must be unique across rows even though no single column alone is. For example, an order_items table could use (order_id, product_id) as its primary key, so one specific product can appear only once per order.

When would you reach for a composite primary key instead of a single auto-incrementing id column?

When a row is naturally and entirely defined by the combination of two or more foreign keys and nothing else needs to identify it — the classic case is a join table linking two other tables, like order_items linking orders and products, where 'this order plus this product' is inherently what the row represents.

Must a foreign key always reference the other table's primary key specifically?

No — a foreign key can reference any column in the other table that's guaranteed unique (typically enforced with a UNIQUE constraint), though referencing the primary key is by far the most common and simplest case.

What is referential integrity, in plain terms?

The guarantee that every foreign key value in a table corresponds to a real, existing row in the table it references — no order can point at a user_id that doesn't exist, keeping related tables consistent with each other.

If a foreign key constraint is enforced, what happens when you try to insert a row whose foreign key value doesn't exist in the referenced table?

The database rejects the insert with a constraint violation error, rather than silently storing a row that points at a nonexistent record.

What does `ON DELETE CASCADE` do on a foreign key?

When the referenced row (say, a user) is deleted, the database automatically deletes every row that references it (that user's orders) as well, instead of leaving them pointing at nothing.

What does `ON DELETE SET NULL` do, and what does it require of the foreign key column?

When the referenced row is deleted, referencing rows have their foreign key column set to NULL instead of being deleted themselves. This requires the foreign key column to allow NULL, since that's the value it will be set to.

What does `ON DELETE RESTRICT` (or the default `NO ACTION` in many databases) do?

It blocks the delete entirely — the database refuses to delete the referenced row as long as any other row still references it, forcing you to deal with those references first.

Running `DELETE FROM users WHERE id = 1;` fails with a foreign key constraint violation. What's the most likely cause?

Some row in another table (like orders) still has a foreign key pointing at that user, and the foreign key's delete behavior is RESTRICT/NO ACTION (a common default) rather than CASCADE or SET NULL, so the database refuses to leave that reference dangling.

Between `ON DELETE CASCADE` and `ON DELETE RESTRICT`, which is generally the safer default, and why?

RESTRICT is generally safer as a default because it forces a deliberate decision about what to do with dependent rows, rather than silently deleting a potentially large, unintended chain of related data. CASCADE is powerful but can remove far more than expected if the relationship chain is deep.

Can a primary key column contain `NULL`?

No. A primary key implies both uniqueness and NOT NULL — a NULL value couldn't reliably identify a specific row, and NULL isn't even considered equal to another NULL, so databases disallow it in primary key columns.

Can a foreign key column contain `NULL`, and what does that mean?

Yes, as long as the column isn't also marked NOT NULL. A NULL foreign key typically means 'this row doesn't currently reference anything' — for example, an order with no assigned sales rep — representing an optional relationship rather than a broken one.

What is the difference between a natural key and a surrogate key?

A natural key is a real-world attribute that's already unique to the row, like a national ID number or an email address. A surrogate key is an artificial value the database generates purely to identify the row, like an auto-incrementing id, with no meaning outside the database.

What's a downside of using a natural key like an email as a primary key, beyond the fact that emails can change?

Natural keys are often longer or more complex than a simple integer, which makes them slower to index and join on, and using them elsewhere as foreign keys propagates that same complexity into every referencing table.

Can a table have more than one column, or combination of columns, that could each serve as a unique identifier? What are those called?

Yes — any column or combination that's unique is a candidate key. A table picks exactly one candidate key to be its actual primary key, but other candidate keys can still be enforced with UNIQUE constraints.

What's the difference between a `UNIQUE` constraint and a `PRIMARY KEY` constraint?

Both enforce uniqueness, but a table can have only one primary key while it can have several UNIQUE constraints on different columns. A UNIQUE column, unlike a primary key, is generally still allowed to contain NULL (commonly even more than one, in most databases, since NULL is never considered equal to another NULL).

What happens if you try to insert a row whose primary key value already exists in the table?

The database rejects the insert with a uniqueness violation error — it never silently overwrites or duplicates an existing row.

Design a `comments` table that belongs to both a `posts` table and a `users` table. What foreign key columns would it need?

Two foreign key columns — post_id INTEGER REFERENCES posts(id) for which post the comment is on, and user_id INTEGER REFERENCES users(id) for who wrote it — alongside its own primary key, id.

What is a self-referencing foreign key? Give an example.

A foreign key column in a table that references the primary key of that same table — for example, an employees table with manager_id INTEGER REFERENCES employees(id), where a manager is just another row in the same table.

Given `orders(id PK, user_id FK -> users.id ON DELETE CASCADE)`, what happens to a user's orders if that user's row is deleted?

Every order whose user_id points at that user is automatically deleted along with it, because of the ON DELETE CASCADE behavior.

Given `orders(id PK, assigned_rep_id FK -> employees.id ON DELETE SET NULL)`, what happens to an order if the employee assigned to it is deleted?

The order row isn't deleted — its assigned_rep_id column is automatically set to NULL, leaving the order in place but now unassigned.

Does adding a foreign key column automatically combine that table's data with the referenced table's data in query results?

No — a foreign key only creates, and optionally enforces, the link between rows. Actually seeing combined data from both tables in one result set still requires writing an explicit JOIN.

Why does inserting an order with a `user_id` that doesn't correspond to any real user cause problems, even without an actively enforced foreign key constraint?

Any later query that joins orders to users to show who placed the order finds nothing for that row, reports can silently undercount or drop it, and there's no way to tell later whether it's a data entry mistake or an intentionally orphaned record — referential integrity exists precisely to prevent this ambiguity.

In a composite primary key like `(order_id, product_id)`, does the order the two columns are listed in change what's enforced?

The uniqueness guarantee itself doesn't depend on the listed order — the combination is what must be unique either way — though the column order can affect which queries make efficient use of the underlying index built to enforce it.

Is it valid, from the database's point of view, to create a table with no primary key at all?

Yes, most databases allow it, but it's rarely a good idea — without a primary key there's no guaranteed way to reliably target, update, or delete one specific row, and other tables can't establish clean foreign key relationships to it.

What's the practical difference between a column that's `NOT NULL` and `UNIQUE` versus that same column being declared `PRIMARY KEY`?

Functionally, NOT NULL plus UNIQUE enforces the same two things a primary key does. The difference is that a table can have only one primary key, signaling the main identifier other tables should reference, while it can have multiple independent NOT NULL and UNIQUE columns alongside it.

If `orders` has `ON DELETE CASCADE` back to `users`, and `order_items` has `ON DELETE CASCADE` back to `orders`, what happens when a single user row is deleted?

Deleting the user cascades to delete all of that user's orders, and each of those deletions in turn cascades to delete all of that order's order_items — a single delete can ripple through multiple linked tables when CASCADE is chained across relationships, which is exactly why RESTRICT is often preferred as a default.