What is the difference between a primary key and a foreign key?

Q: What is the difference between a primary key and a foreign key?

**Target Audience:** Beginners meeting relational tables for the first time, and anyone who wants the precise rules an interviewer expects. **Difficulty:** Easy **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL and SQL Server differences are cited from their manuals. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First Picture a library. Every member gets a membership card with a number on it, and no two members ever share a number. That number is how the library tells members apart, even two people both called "Chen". Now picture the loans ledger. Each line says "book X was borrowed by member 1

The problem

Question presented to candidate: "Every table in our schema has an id column, and some tables also have columns like customer_id. Explain what a primary key is, what a foreign key is, how they work together, and what the database actually enforces for each."

What a strong answer should cover:

A primary key uniquely identifies each row of its own table: unique, never NULL, one per table (it may span several columns)
A foreign key is a column in a child table whose values must match a primary key (or unique) value in the parent table, or be NULL
What each enforces: duplicates and NULLs are rejected for the primary key; orphan inserts and deletes of referenced parents are rejected for the foreign key
Indexes: the primary key always gets one; in PostgreSQL the foreign key column does not, which matters for joins and parent deletes
Engine detail: SQLite only enforces foreign keys after PRAGMA foreign_keys = ON, and only allows NULL in a non-INTEGER primary key for legacy reasons
Cascade options (ON DELETE CASCADE, SET NULL, RESTRICT) as the policy for what happens to children

Code / implementation expected: Optional — a two-table schema with PRIMARY KEY and REFERENCES, and one join that follows the key.

Schema used by the examples:

primary-key
foreign-key

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners meeting relational tables for the first time, and anyone who wants the precise rules an interviewer expects. Difficulty: Easy Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL and SQL Server differences are cited from their manuals.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

Picture a library. Every member gets a membership card with a number on it, and no two members ever share a number. That number is how the library tells members apart, even two peopl

The solution is hidden so you can try first

14 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLFollow the foreign key: each order joined to the customer its customer_id points at

SQLSQLite: foreign keys are off until you switch them on, so an orphan slips in

SQLAsk SQLite which column is the primary key and where the foreign key points

PostgreSQL: the primary key and UNIQUE get indexes, the foreign key column does not

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.