What is the difference between a primary key and a unique constraint?

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

**Target Audience:** Beginners who know what a table is, and candidates who want the NULL details interviewers like to probe. **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. SQL Server and MySQL behaviour is cited from their documentation. > **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 Think of a passport office. Every citizen has exactly one passport number, printed on every document the office keeps about them. That number is how every other office refers to the person: the tax office, the border records, the visa files. Citizens also have other things that must be uniqu

The problem

Question presented to candidate: "Our users table has an id primary key, but email and username must also be unique. Both a primary key and a unique constraint stop duplicates, so what is actually different about them, and how do you decide which column gets which?"

What a strong answer should cover:

Both reject duplicate values and both are backed by a unique index
A table has at most one primary key but any number of unique constraints
The primary key is always NOT NULL; a unique column allows NULL, and by default several NULLs, because NULL is not equal to NULL
Engine differences: PostgreSQL 15+ NULLS NOT DISTINCT, SQL Server allowing only one NULL, SQLite letting NULL into some primary keys
The primary key is the identity other tables reference (a foreign key can also target a unique key), and in MySQL InnoDB it is the clustered index
Typical design: a stable surrogate primary key plus unique constraints on natural keys like email

Code / implementation expected: Optional — a table with one PRIMARY KEY and two UNIQUE columns, showing the NULL behaviour.

Schema used by the examples:

constraints

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 who know what a table is, and candidates who want the NULL details interviewers like to probe. 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. SQL Server and MySQL behaviour is cited from their documentation.

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

Think of a passport office. Every citizen has exactly one passport number, printed on every document the office keeps about them. That number is how every other office refers to the person: the

The solution is hidden so you can try first

12 min read · 9 sections

Run the code

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

SQLA UNIQUE column holds several NULLs: unique means unique among the values that exist

SQLSQLite: each UNIQUE constraint creates its own index; INTEGER PRIMARY KEY needs none

SQLA foreign key may target a UNIQUE column, not only the primary key

PostgreSQL: the catalog shows one constraint of type p and three of type u, and the primary key column became NOT NULL

SELECT c.conname, c.contype, a.attname, a.attnotnull
FROM pg_constraint AS c
JOIN pg_attribute AS a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
WHERE c.conrelid = 'users'::regclass
ORDER BY c.contype, c.conname;

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.