skip to content

First normal form also requires that every row be uniquely identifiable. What goes wrong in a table that permits exact duplicate rows and has no primary key, and how would you repair one that already exists?

level: middleimportance: should knowfreq 45%

answer

  1. relation = set, SQL table = bag
  2. cannot DELETE just one duplicate
  3. joins multiply, totals inflate silently
  4. ORM/CDC need a row identity
  5. surrogate key alone does not stop duplicates

basics

~20 s

Duplicates are indistinguishable, so you cannot update or delete just one of them, an ORM or replication cannot identify a row, and joins multiply counts silently. Repair: find the duplicate groups, decide which to keep, delete the rest, then add a primary key and a unique constraint on the business identity.

solid answer

~1 min

A relation is a set of tuples, so every tuple is distinct and addressable. SQL tables are bags and allow duplicates, so the property has to be recovered by declaring a key. Without one: - **You cannot address a single row.** `DELETE FROM t WHERE …` matching two identical rows deletes both; there is no predicate that selects exactly one. - **Correctness rots quietly.** A duplicated order line double-counts revenue; a join against a table with duplicates multiplies rows in the result and inflates every aggregate downstream. - **Tooling breaks.** ORMs need an identity to track and update an entity; logical replication and change-data-capture need a key to apply a change; audit trails cannot reference the row. - **Duplicates keep arriving**, because nothing rejects a retried insert. Repair is three steps: group by the business identity to find duplicates and decide a survivor rule (lowest id, latest timestamp, merged values); delete or archive the losers; then add the constraints — a primary key (surrogate if no natural key is trustworthy) plus a unique constraint on the real business identity so the problem cannot recur. The unique constraint is the part that actually prevents recurrence; a surrogate key alone does not.

code

sql · 5 lines
sql
SELECT email, COUNT(*) AS n, MIN(id) AS keep_id
FROM customer
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY n DESC;

go deeper

for a junior

Say that duplicate rows cannot be told apart, so you cannot update or delete just one, and that a primary key is what makes rows identifiable.

for a middle

Add the join-multiplication effect on aggregates and the repair sequence: find groups, choose a survivor, delete, then add both a primary key and a unique constraint on the business identity.

for a senior

Bring in ORM identity, logical replication needing a replica identity, retried inserts as the usual source of duplicates, and batched de-duplication with an archive table.

for a principal

Discuss natural versus surrogate key policy across the schema, idempotency at the write path so constraints are not the only defence, and when a deliberately keyless append-only landing table is a justified exception.

## Why uniqueness belongs to 1NF In the relational model a relation is a *set* of tuples. Sets contain no duplicates, so every tuple is distinguishable from every other and can be referred to. SQL deliberately departed from this — a table is a bag, `SELECT` can return duplicates, and a table with no key is legal. That is why 1NF is usually stated with an explicit clause about row uniqueness and why practitioners phrase it as "declare a primary key". Two other consequences of relation-as-set are worth saying in the same breath: **row order carries no information**, and **column order carries no information**. Anything that means something — priority, sequence — has to be a column. ## What breaks without a key **You cannot target a row.** With two byte-identical rows, no `WHERE` clause distinguishes them. Deleting "one of them" requires engine-specific tricks (a hidden row identifier, or `DELETE … LIMIT 1` where supported, or rebuilding the table from a de-duplicated copy). Updating one and not the other is likewise impossible. Every maintenance operation becomes bespoke. **Silent arithmetic errors.** Duplicates do not announce themselves; they show up as numbers that are slightly wrong. A duplicated payment row inflates a total. Worse, joining a clean table to a duplicated one multiplies rows: a customer with two identical address rows produces two result rows, so `SUM(order_total)` doubles. This class of bug survives for months because nothing errors. **Tooling assumes identity.** ORMs cache and dirty-track entities by identifier; without one they cannot map a row to an object reliably. Logical replication and change-data-capture need a replica identity to apply an `UPDATE` or `DELETE` on the subscriber — a keyless table typically forces full-row matching or is refused outright. Any audit or outbox record that wants to point at "the row that changed" has nothing to point at. Foreign keys from other tables are impossible, so the table can never be referenced. **Retries create duplicates.** Distributed systems retry. Without a unique constraint on the business identity, a retried insert after a timeout silently creates a second row. The constraint is what makes the second attempt fail cleanly and lets the caller treat it as "already done". **Performance.** Most engines cluster or organise storage around the primary key; a keyless heap has no such structure, and every lookup that would have been a key probe becomes a scan. ## Repairing an existing table **1. Measure.** Group by the columns that constitute the *business* identity — not by every column, since near-duplicates differing in one timestamp are the interesting case — and count. Report how many groups have more than one row and how badly they differ. **2. Decide the survivor rule with the domain owner.** Options: keep the earliest, keep the latest, or merge — take the non-null value per column across the group. Merging is common when duplicates were created by partial writes. This is a business decision, not a technical one; do not guess. **3. Preserve before deleting.** Copy the losing rows into an archive table in the same migration, so the operation is reversible for a bounded period. Also repoint anything that referenced the losers, if such references exist. **4. Delete the losers**, in batches on a large table so you do not hold a huge transaction. **5. Add the constraints.** A primary key — a surrogate integer/UUID if no natural key is trustworthy — *and*, crucially, a unique constraint on the business identity. Adding only a surrogate key makes every row technically distinct while allowing exactly the same logical duplicates to keep arriving; that is the most common half-fix. **6. Fix the source.** Find out how duplicates were created: a retried request with no idempotency key, an import job with no upsert, a race between two writers. The constraint turns the failure into a visible error; the application still has to handle it, typically as an upsert or by treating the violation as success. ## Natural versus surrogate A natural key (email, ISBN, order number) makes the uniqueness rule self-documenting but is only safe if the value is genuinely unique, genuinely stable and genuinely never null — most business identifiers fail at least one of those eventually. The common compromise is a surrogate primary key for references and joins plus a unique constraint expressing the natural identity, which gives stable references and enforced business uniqueness at once. ## A note on deliberate keyless tables High-volume append-only staging or event-landing tables are sometimes left without a key on purpose, to keep inserts as cheap as possible. That is a considered trade-off for a table whose rows are never individually updated and which is consumed in bulk. It should be written down, and the de-duplication has to happen somewhere downstream instead.

  • You add a surrogate primary key to a table full of logical duplicates. Is the problem solved?
    No. A surrogate key makes every row technically distinct and addressable, which fixes the ability to update or delete one row, but it places no restriction on the business identity. The same customer can still be inserted twice with two different generated ids. You need a unique constraint on the columns that express the real identity for the duplicates to stop arriving.
  • How do you delete duplicates from a large table without holding a huge transaction?
    Identify the survivor per group first and materialise the list of ids to delete, then delete in bounded batches with a commit between them, so no single transaction accumulates millions of row locks or a giant undo footprint. Archive the losing rows before deleting so the operation stays reversible. On a very large table it is often cheaper to build a de-duplicated copy and swap it in.

Two identical unnumbered tickets in a raffle drum: you can hold them, but you cannot say which one won, and you cannot take one out without taking both.

saying these in an interview costs you the question

  • Believing a table without a primary key is fine as long as the application never inserts duplicates
  • Thinking a surrogate key alone prevents logical duplicates
  • Not recognising that joining to a table with duplicates silently multiplies aggregate results
  • Deleting duplicates without agreeing a survivor rule with the domain owner or archiving the losers
  • Assuming row order or physical position can be used to identify a specific row

context

resolving links…