Unique Key and Candidate Key
UNIQUE Constraint, NULLs, and Minimal Super Keys
LinkedIn Hook
"You added a UNIQUE constraint to
Most developers learn PRIMARY KEY on day one and then treat UNIQUE as "the same thing, but less important." It is not. A UNIQUE constraint has its own rules for NULLs, its own index, its own relationship to candidate keys, and its own traps that only show up in production when a duplicate somehow sneaks past.
The relational model is actually strict and elegant here. A table can have many candidate keys — every minimal combination of columns that uniquely identifies a row. You pick exactly one to be the primary key. The rest become alternate keys, usually enforced with UNIQUE. The primary key cannot be NULL. The alternate keys can — and in Postgres, by default, multiple NULLs are allowed in the same UNIQUE column, because SQL treats NULL as "unknown" and two unknowns are not equal.
Postgres 15 finally gave us
NULLS NOT DISTINCTso you can opt into the stricter behaviour. SQL Server has always treated NULLs as equal in UNIQUE. MySQL allows multiple NULLs. Every engine has a different default, and the interview question is almost always "what does your database actually do."In Lesson 2.3, I break down UNIQUE vs PRIMARY KEY, candidate keys, NULL semantics across engines, and how to identify every candidate key from a given table.
Read the full lesson -> [link]
#Database #SQL #PostgreSQL #DataModeling #InterviewPrep #BackendDevelopment
What You'll Learn
- The formal definition of super key, candidate key, primary key, and alternate key
- How UNIQUE differs from PRIMARY KEY in NULL handling, count per table, and indexing
- Why Postgres allows multiple NULLs in a UNIQUE column by default, and how
NULLS NOT DISTINCTchanges that - How multi-column UNIQUE constraints enforce composite uniqueness
- How to identify every candidate key from a given table definition
- The performance implications of UNIQUE creating an implicit B-tree index
- When to use UNIQUE vs PRIMARY KEY vs a plain index
The Passport vs Email Analogy — Two Valid IDs, One You Chose
Imagine you are building a profile of a person for a government system. You need to uniquely identify them. You could use their email address, their passport number, their national ID, or their phone number. Every one of these is, in principle, a unique identifier — no two people share the same passport number, and no two people share the same email address.
But when you fill in the official form, only one box says "primary identifier." You pick passport number. It goes at the top of the file, it is printed on every badge, and it is the value every other system uses to reference this person. The email and phone number are still unique — if someone tries to register with an email that is already on file, the system rejects it — but they are not the chosen identifier. They are alternate keys.
That is exactly the relationship between candidate keys and the primary key in a relational database. A table might have several columns or combinations of columns that could each uniquely identify a row — these are candidate keys. The database designer picks exactly one to be the primary key (it must be NOT NULL, it gets a clustered index in SQL Server, and foreign keys typically reference it). The remaining candidate keys are enforced with UNIQUE constraints and called alternate keys.
The subtle twist is NULL. A passport number is always filled in. But an email address might be missing for users who signed up via phone, and in SQL that "missing" is represented as NULL. Can two rows both have a NULL email and still satisfy UNIQUE? In Postgres, MySQL, and SQLite — yes, by default. In SQL Server — no. That single difference catches developers every time.
+---------------------------------------------------------------+
| KEY HIERARCHY — NESTED DIAGRAM |
+---------------------------------------------------------------+
| |
| +------------------------------------------------------+ |
| | SUPER KEY | |
| | Any set of columns that uniquely identifies a row. | |
| | May contain extra, unnecessary columns. | |
| | | |
| | +------------------------------------------------+ | |
| | | CANDIDATE KEY | | |
| | | A MINIMAL super key — remove any column and | | |
| | | uniqueness is lost. A table can have many. | | |
| | | | | |
| | | +-------------------+ +------------------+ | | |
| | | | PRIMARY KEY | | ALTERNATE KEYS | | | |
| | | | The ONE chosen | | All other | | | |
| | | | candidate key. | | candidates. | | | |
| | | | NOT NULL. | | Enforced with | | | |
| | | | One per table. | | UNIQUE. | | | |
| | | +-------------------+ +------------------+ | | |
| | +------------------------------------------------+ | |
| +------------------------------------------------------+ |
| |
+---------------------------------------------------------------+Formal Definitions
Super Key. A set of one or more columns whose combined values are unique across every row of a table. A super key may contain redundant columns — (user_id, email, created_at) is a super key if user_id alone already uniquely identifies rows, because adding more columns to a unique set keeps it unique.
Candidate Key. A super key that is minimal: no proper subset of it is itself a super key. If you remove any column, uniqueness breaks. (user_id) is a candidate key. (user_id, email) is a super key but not a candidate key, because dropping email still leaves you with a unique set.
Primary Key. The single candidate key chosen by the designer to be the row's official identifier. It is declared PRIMARY KEY and is implicitly NOT NULL. A table has at most one primary key.
Alternate Key. Every candidate key that was not chosen as the primary key. Alternate keys are enforced in SQL using the UNIQUE constraint.
Unique Key. Informal term, usually meaning any column (or set of columns) with a UNIQUE constraint. In practice, "unique key" and "alternate key" are used interchangeably, though "unique key" is the physical SQL feature and "alternate key" is the conceptual relational term.
UNIQUE vs PRIMARY KEY — The Differences That Matter
Both UNIQUE and PRIMARY KEY enforce uniqueness. The differences are in NULL handling, count per table, and defaults. The table below captures every practical distinction you will be asked about in an interview.
| Property | PRIMARY KEY | UNIQUE |
|---|---|---|
| Enforces uniqueness | Yes | Yes |
| Allows NULL | No — NOT NULL is implicit | Yes — NULL is allowed |
| Multiple NULLs allowed | N/A (no NULLs at all) | Yes in Postgres/MySQL/SQLite, No in SQL Server/Oracle by default |
| Count per table | Exactly one | Many |
| Creates an index | Yes (automatic) | Yes (automatic) |
| Default clustered index | Yes in SQL Server, No in Postgres | No |
| Referenced by FK by default | Yes (most common target) | Allowed, less common |
| Can be changed later | Yes, but disruptive | Yes, drop and recreate |
| Implicit NOT NULL | Yes | No — you must add NOT NULL separately |
The row about NULL semantics is the one that trips everyone up. SQL defines NULL as "unknown," and two unknown values are not considered equal. Under strict SQL, UNIQUE should therefore allow multiple NULLs, because no pair of NULLs is provably equal. Postgres, MySQL, and SQLite follow this reading. SQL Server and Oracle deviate — they treat NULLs as equal for UNIQUE purposes and allow at most one NULL per UNIQUE column.
Runnable Example — CREATE TABLE with Multiple UNIQUE Constraints
Let us build a realistic users table that has one primary key and several alternate keys. The users table needs to be uniquely identifiable by its internal id, its email, its username, and the combination of phone_country_code and phone_number. Every one of these is a candidate key, and the designer picks id as the primary key.
-- Postgres 15+ syntax
CREATE TABLE users (
-- Primary key: the chosen candidate key. Synthetic BIGSERIAL
-- guarantees no duplicates and no NULLs.
id BIGSERIAL PRIMARY KEY,
-- Alternate key 1: email. UNIQUE allows NULLs by default in
-- Postgres, so users who signed up via phone only can have
-- email = NULL. Multiple NULLs are permitted.
email VARCHAR(255) UNIQUE,
-- Alternate key 2: username. We want every user to have one,
-- so we add NOT NULL alongside UNIQUE. Without NOT NULL, the
-- constraint would allow multiple rows with NULL username.
username VARCHAR(50) UNIQUE NOT NULL,
-- Alternate key 3: a COMPOSITE key over two columns.
-- Declared as a named table-level constraint so we can
-- reference it in error messages and migrations.
phone_country VARCHAR(4),
phone_number VARCHAR(20),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
-- Multi-column UNIQUE: the PAIR must be unique. A single
-- phone_number can repeat across different country codes.
CONSTRAINT uniq_users_phone UNIQUE (phone_country, phone_number)
);Now observe the NULL behaviour:
-- All of these succeed in Postgres by default:
INSERT INTO users (username, email) VALUES ('alice', 'a@example.com');
INSERT INTO users (username, email) VALUES ('bob', 'b@example.com');
INSERT INTO users (username, email) VALUES ('carol', NULL);
INSERT INTO users (username, email) VALUES ('dave', NULL); -- ALLOWED!
INSERT INTO users (username, email) VALUES ('eve', NULL); -- ALSO allowed
-- This fails — username is NOT NULL UNIQUE, and 'alice' is taken:
INSERT INTO users (username, email) VALUES ('alice', 'new@example.com');
-- ERROR: duplicate key value violates unique constraint "users_username_key"If the business rule is "every email must be distinct, and two NULLs count as duplicates," Postgres 15 introduced NULLS NOT DISTINCT to opt into that stricter behaviour without a workaround.
NULLS NOT DISTINCT — Postgres 15+
Before Postgres 15, the standard workaround for "treat NULLs as equal in UNIQUE" was a partial unique index on the non-NULL subset, which was clever but awkward. Postgres 15 added a clean declarative option:
-- Postgres 15+: treat NULLs as EQUAL for uniqueness purposes.
-- This rejects the 4th and 5th NULL inserts above.
CREATE TABLE users_strict (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255),
username VARCHAR(50) NOT NULL,
-- At most ONE row may have email = NULL.
CONSTRAINT uniq_users_strict_email
UNIQUE NULLS NOT DISTINCT (email)
);For older Postgres versions (or as a more surgical tool), a partial unique index achieves the same thing for non-NULL values while still allowing many NULLs:
-- Pre-15 idiom: UNIQUE across rows where email IS NOT NULL,
-- while NULL rows remain uncounted.
CREATE UNIQUE INDEX uniq_users_email_notnull
ON users (email)
WHERE email IS NOT NULL;+---------------------------------------------------------------+
| NULL SEMANTICS BY ENGINE |
+---------------------------------------------------------------+
| |
| ENGINE | Multiple NULLs in UNIQUE? | Opt-out? |
| --------------|---------------------------|------------------|
| PostgreSQL | YES (default) | NULLS NOT DISTINCT|
| MySQL (InnoDB)| YES | No clean syntax |
| SQLite | YES | No |
| SQL Server | NO (one NULL max) | Filtered index |
| Oracle | Depends on single/multi | Function index |
| | column; multi allows many | |
| |
+---------------------------------------------------------------+Identifying Candidate Keys — Worked Example
Interviewers love to hand you a table and ask "list all the candidate keys." The process is mechanical once you internalise it.
Consider an enrollments table recording which student is enrolled in which course in which term:
+---------------------------------------------------------------+
| enrollments |
+------------+------------+--------+--------+-------------------+
| student_id | course_id | term | seat | enrolled_at |
+------------+------------+--------+--------+-------------------+
| 101 | CS201 | 2025F | A-14 | 2025-08-15 09:00 |
| 102 | CS201 | 2025F | A-15 | 2025-08-15 09:01 |
| 101 | MA101 | 2025F | B-03 | 2025-08-15 09:02 |
| 103 | CS201 | 2025S | A-14 | 2025-02-01 10:00 |
+------------+------------+--------+--------+-------------------+Business rules given by the interviewer:
- A student can only enrol in a given course once per term.
- Each seat in a given course and term is assigned to at most one student.
enrolled_atis a precise timestamp and no two enrolments happen at the exact same instant globally.
Step 1 — List functional dependencies. From the rules:
(student_id, course_id, term)-> uniquely identifies one row (rule 1).(course_id, term, seat)-> uniquely identifies one row (rule 2).(enrolled_at)-> uniquely identifies one row (rule 3).
Step 2 — Check minimality. For each candidate, try to remove a column and see if uniqueness breaks:
(student_id, course_id, term): droptermand student 101 appears twice for course CS201 across terms. Dropcourse_idand student 101 has multiple courses in 2025F. Dropstudent_idand course CS201 has many students. All three columns are needed. Candidate key.(course_id, term, seat): same analysis — all three needed. Candidate key.(enrolled_at): single column, already minimal. Candidate key.
Step 3 — List alternate keys and pick a primary key. Three candidate keys exist. The designer picks one as the primary key. A synthetic surrogate key (BIGSERIAL id) is often added instead, making all three business keys alternate keys. If we must pick from the natural candidates, (student_id, course_id, term) is the usual choice because it is the most meaningful in application code.
Step 4 — Express in SQL.
CREATE TABLE enrollments (
id BIGSERIAL PRIMARY KEY,
student_id BIGINT NOT NULL REFERENCES students(id),
course_id VARCHAR(16) NOT NULL REFERENCES courses(id),
term VARCHAR(8) NOT NULL,
seat VARCHAR(8) NOT NULL,
enrolled_at TIMESTAMPTZ NOT NULL,
-- Alternate key 1: one enrolment per student per course per term
CONSTRAINT uniq_enrol_student_course_term
UNIQUE (student_id, course_id, term),
-- Alternate key 2: one student per seat per course per term
CONSTRAINT uniq_enrol_course_term_seat
UNIQUE (course_id, term, seat),
-- Alternate key 3: globally unique timestamp
CONSTRAINT uniq_enrol_enrolled_at
UNIQUE (enrolled_at)
);Every UNIQUE constraint here protects a real business rule. Drop one and that rule becomes an application-layer concern, which means a race condition will eventually break it.
UNIQUE Creates an Index — Performance Implications
One fact that surprises developers: every UNIQUE constraint in every major database creates an implicit B-tree index on the constrained columns. This is necessary — the engine has to check uniqueness on every INSERT and UPDATE, and without an index that would require a full table scan. With an index, the lookup is O(log n).
This has three consequences:
- You do not need a separate
CREATE INDEXon UNIQUE columns. The constraint already gave you one. Adding another is a waste of disk space and write cost. - UNIQUE slows down writes. Every INSERT must check the index and update it. On a hot table with many UNIQUE constraints, write throughput drops measurably.
- UNIQUE speeds up reads. Lookups by a UNIQUE column are index-backed and as fast as primary-key lookups.
+---------------------------------------------------------------+
| WHAT UNIQUE ACTUALLY BUILDS |
+---------------------------------------------------------------+
| |
| CREATE TABLE t ( |
| id BIGSERIAL PRIMARY KEY, -- builds B-tree index |
| email VARCHAR UNIQUE -- builds ANOTHER B-tree |
| ); |
| |
| Postgres internally creates: |
| - t_pkey (unique B-tree on id) |
| - t_email_key (unique B-tree on email) |
| |
| Every INSERT: |
| 1. Insert row into heap |
| 2. Update t_pkey |
| 3. Update t_email_key |
| 4. Verify no duplicate in either index |
| |
+---------------------------------------------------------------+Napkin AI Visual Prompt: "Dark gradient (#0a0f1f -> #111a2e). Center: a nested Venn diagram — outermost ring labelled 'Super Key' in rose (#ff5c8a), middle ring 'Candidate Key' in sky blue (#4fc3f7), innermost solid circle 'Primary Key' in bright white. To the side, a split comparison showing UNIQUE vs PRIMARY KEY with a NULL cell highlighted on the UNIQUE side. Below, three example tables with arrows marking candidate keys in sky blue. White monospace labels throughout. Subtle grid pattern overlay."
Common Mistakes
1. Expecting UNIQUE to reject multiple NULLs.
This is the number one surprise for developers moving from SQL Server to Postgres or MySQL. In Postgres, a UNIQUE column happily accepts any number of NULLs, because NULL is "unknown" and two unknowns are not considered equal under SQL semantics. If your business rule is "email must be unique AND two missing emails should collide," you need NULLS NOT DISTINCT in Postgres 15+, a partial unique index in older versions, or simply NOT NULL UNIQUE so the problem never arises.
2. Using UNIQUE instead of PRIMARY KEY without thinking.
A beginner reads the docs, sees that both enforce uniqueness, and declares user_id INT UNIQUE instead of user_id INT PRIMARY KEY. The table now has no primary key at all. The consequences: NULL is allowed in user_id (surprise duplicates), some ORMs refuse to map the table, foreign keys are harder to wire up, and in SQL Server there is no clustered index by default so range scans are slower. PRIMARY KEY is the stronger, more declarative choice — use it.
3. Forgetting that UNIQUE creates an index.
Developers often add a UNIQUE constraint and then separately run CREATE INDEX ON t (email) because "we query by email a lot." The second index is redundant — the UNIQUE constraint already built a B-tree on email. You are now paying double the write cost and double the disk for zero read benefit. Check with \d tablename in psql or SHOW INDEX FROM t in MySQL before adding indexes.
4. Declaring UNIQUE on a nullable column that must always have a value.
Writing email VARCHAR UNIQUE without NOT NULL tells the database "email is optional, but when present it must be unique." If your business rule is actually "every user must have an email, and emails must be distinct," the correct declaration is email VARCHAR NOT NULL UNIQUE. Leaving off NOT NULL is a silent bug waiting for someone to insert a row with a missing email.
Interview Questions
1. "What is the difference between a PRIMARY KEY and a UNIQUE constraint?"
Both enforce that no two rows share the same value (or combination of values) in the constrained columns, and both create a B-tree index automatically. The differences are three. First, a PRIMARY KEY is implicitly NOT NULL — you cannot insert a row with NULL in a primary key column — while a UNIQUE column allows NULL unless you explicitly add NOT NULL. Second, a table can have exactly one primary key but many UNIQUE constraints; the primary key represents the single chosen row identifier, while UNIQUE constraints enforce alternate keys and other uniqueness business rules. Third, some engines treat the primary key specially: SQL Server creates a clustered index on the primary key by default (physically ordering the table by it), and most ORMs and foreign key relationships default to targeting the primary key. Semantically, a PRIMARY KEY is the one canonical candidate key chosen by the designer, and UNIQUE constraints enforce every other candidate key.
2. "Can a UNIQUE column be NULL? And can it have multiple NULL values?"
Yes, a UNIQUE column can be NULL unless you also declare it NOT NULL. The more interesting question is whether multiple NULLs are allowed, and the answer depends on the database. Postgres, MySQL (InnoDB), and SQLite all allow multiple NULLs in a UNIQUE column by default, following the strict SQL reading that NULL means "unknown" and two unknown values cannot be proven equal. SQL Server and Oracle (for single-column UNIQUE) treat NULLs as equal and allow at most one NULL per UNIQUE column. Postgres 15 added the UNIQUE NULLS NOT DISTINCT option which opts into treating NULLs as equal, and older Postgres versions used a partial unique index (WHERE email IS NOT NULL) as a workaround. If your requirement is "no two rows should have the same email AND rows without an email should be treated as duplicates," you must explicitly configure this — do not rely on defaults, because they differ across engines.
3. "What is a candidate key and how do you identify all candidate keys in a given table?"
A candidate key is a minimal super key — a set of columns whose values uniquely identify every row, such that removing any column from the set would break uniqueness. A table can have multiple candidate keys, and the database designer picks exactly one to be the primary key; the rest become alternate keys enforced via UNIQUE. To identify candidate keys you start from the table's functional dependencies (the business rules about which columns determine which other columns). For each combination of columns that determines the whole row, check minimality by trying to drop each column and seeing whether uniqueness survives. If no proper subset is itself a super key, the combination is a candidate key. For example, in an enrollments table where a student can enrol in a course only once per term, (student_id, course_id, term) is a candidate key because all three columns are required — dropping any one allows duplicates. If the table also guarantees one seat per course per term, (course_id, term, seat) is a second candidate key. Surrogate primary keys like BIGSERIAL id are added on top and become a trivial candidate key as well.
4. "What is the difference between a super key and a candidate key?"
A super key is any set of columns that uniquely identifies rows, regardless of whether it contains redundant columns. A candidate key is a super key that is minimal — no column can be removed without losing uniqueness. Every candidate key is a super key, but most super keys are not candidate keys. For example, if user_id alone uniquely identifies rows in a users table, then (user_id), (user_id, email), and (user_id, email, created_at) are all super keys — they all uniquely identify rows. But only (user_id) is a candidate key, because the other two contain redundant columns that can be dropped without breaking uniqueness. The concept matters because normalisation theory (2NF, 3NF, BCNF) is defined in terms of candidate keys, not super keys — you need to reason about the minimal identifiers of a table to detect and fix redundancy.
5. "If UNIQUE automatically creates an index, should you ever add a separate index on a UNIQUE column?"
Almost never. Every major database (Postgres, MySQL, SQL Server, Oracle, SQLite) creates an implicit B-tree index when you declare a UNIQUE constraint, because checking uniqueness on every INSERT and UPDATE would otherwise require a full table scan. That index is fully usable by query planners for lookups, range scans, and ORDER BY — adding a second index on the same column gives you zero read benefit while doubling write cost and disk usage. The only legitimate reasons to add another index are: you need a different index type (GIN, GiST, hash) that the unique B-tree does not provide, you need a partial index covering only a subset of rows, you need a multi-column index that starts with the UNIQUE column but includes others for covering lookups, or you need a functional index on an expression derived from the column. Before adding any index, always check what already exists with \d tablename in psql or SHOW INDEX FROM t in MySQL.
Quick Reference — Unique and Candidate Key Cheat Sheet
+---------------------------------------------------------------+
| UNIQUE AND CANDIDATE KEY CHEAT SHEET |
+---------------------------------------------------------------+
| |
| KEY TERMINOLOGY: |
| Super Key -> any unique set of columns |
| Candidate Key -> a MINIMAL super key |
| Primary Key -> the chosen candidate key (NOT NULL) |
| Alternate Key -> other candidate keys (UNIQUE) |
| |
| DECLARE: |
| id BIGSERIAL PRIMARY KEY |
| email VARCHAR UNIQUE NOT NULL |
| CONSTRAINT uniq_phone UNIQUE (country, number) |
| |
| NULL BEHAVIOUR (default): |
| Postgres / MySQL / SQLite -> many NULLs allowed |
| SQL Server / Oracle -> one NULL max |
| Postgres 15+ opt-in -> UNIQUE NULLS NOT DISTINCT |
| |
| PRE-15 POSTGRES IDIOM: |
| CREATE UNIQUE INDEX ... ON t (email) |
| WHERE email IS NOT NULL; |
| |
| INDEX FACT: |
| UNIQUE always builds an implicit B-tree index. |
| Do NOT add a second index on the same column. |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| KEY RULES |
+---------------------------------------------------------------+
| |
| 1. A table has ONE primary key, MANY unique keys |
| 2. PRIMARY KEY = UNIQUE + NOT NULL + "the chosen one" |
| 3. UNIQUE allows NULL unless you add NOT NULL |
| 4. Postgres allows multiple NULLs in UNIQUE by default |
| 5. Candidate key = minimal super key |
| 6. Every UNIQUE creates a B-tree index — do not duplicate |
| 7. Name your composite UNIQUE constraints for clean errors |
| 8. Normalisation (2NF/3NF/BCNF) is defined via candidate keys |
| |
+---------------------------------------------------------------+| Concern | Wrong Way | Right Way |
|---|---|---|
| Unique email required | email VARCHAR UNIQUE | email VARCHAR NOT NULL UNIQUE |
| Strict NULL collision (PG 15+) | Partial index hack | UNIQUE NULLS NOT DISTINCT (col) |
| Multi-column uniqueness | Two single-column UNIQUEs | UNIQUE (col_a, col_b) |
| Row identifier | id INT UNIQUE (nullable!) | id BIGSERIAL PRIMARY KEY |
| Fast lookup by UNIQUE col | CREATE INDEX on top | Rely on implicit UNIQUE index |
| Expressing alternate keys | App-layer checks | UNIQUE table constraints |
| Naming constraints | Auto-generated names | CONSTRAINT uniq_<table>_<cols> |
| Candidate key analysis | Guess the primary key | Derive from functional deps |
Prev: Lesson 2.2 -- Foreign Key Next: Lesson 2.4 -- Composite and Super Key
This is Lesson 2.3 of the Database Interview Prep Course -- 12 chapters, 58 lessons.