Duplicate rows in a table: choose a survivor, repoint references, keep a way back, then add the constraint

How the same thing ends up in a table twice

Duplicates arrive by a handful of routes. Nothing enforced uniqueness in the first place. A unique constraint existed but a null in the key column slipped past it, because the PostgreSQL manual notes that by default two nulls are not considered equal. An import or a retried job inserted the same row twice. Two systems each created a record for the same person. Whatever the route, finding the cause matters, because without removing it the duplicates return after you tidy up.

The harder problem is that other tables reference the copies. Orders point at one customer row, invoices at another, tickets at a third. Deleting the extras either fails on a foreign key, leaves rows pointing at nothing or, where the key says CASCADE, deletes the related rows too. PostgreSQL documents ON DELETE CASCADE as deleting the referencing rows, which is what you want for order lines and exactly what you do not want for a customer's history.

• List every table and column that references the duplicated table, declared or not.
• Check each foreign key's ON DELETE action before running any delete.

Define what makes two rows the same, and who survives

Write the matching rule as exactly as you can: the same reference number, the same email after trimming and lower-casing, the same pair of fields. Exact or deterministic rules can be run by script; rules that need judgement, such as similar names or addresses, need a person per case and are a different job. Then write the survivor rule: the oldest row, the newest, the most complete, the one with the most references. Get the person who owns the data to approve both in writing.

Review a sample of groups by eye, with personal values masked, before anything runs. Count the groups. A rule that finds a quarter of the table is probably wrong, not the data. If you test on a copy with personal data masked, the masking must keep your rule's equivalence: values that match under the rule must still match after masking, and values that did not must not. Count the groups on production and on the masked copy; the two numbers must be equal.

• Decide how nulls and empty strings are treated in the key.
• Record any rows that must be left exactly as they are.

Merge in a safe order

Work on a restored copy first. For each group, copy every extra row in full into an archive table, then repoint every reference from the extra rows to the survivor, in every dependent table, writing a log line for each reference moved: table, primary key, old key, new key. Only then remove the extras. Do it in small transactions that can be re-run. A table mapping each removed id to its survivor is not a way back: it cannot restore what a deleted row contained, and it cannot say which referencing rows were repointed, because rows that already pointed at the survivor look the same. The archive and the log can. Write a reverse script that puts the archived rows back and sets each moved reference back, run it on the copy, and compare table checksums with those taken before the merge, after removing any rows a test insert script added to the copy while you rehearsed the guard. If a dependent table has its own uniqueness rule, such as one membership per customer, repointing may itself create collisions that need a rule.

Finish with a reconciliation: row counts before and after, references repointed per table, extras removed and archived, and a check that no reference points at a removed row. The numbers must account for every row. If they do not, stop and find out why before going near production. Your tested restore point is the other way back, but it also loses every write made since it was taken.

• Repoint, check, then delete; never delete first.
• Keep the archive, the reference log and the before checksums with the change record.

Add the guard, and what the paid job covers

Once the table is clean, add the constraint that makes the rule permanent. On PostgreSQL that is a unique constraint on the key, or a unique partial index if the rule covers only some rows, since the manual says a restriction covering only some rows cannot be written as a unique constraint; use NULLS NOT DISTINCT if nulls should count as equal. MySQL has no partial indexes, and its UNIQUE index permits many NULL values. For a rule that covers only some rows, index an expression that is NULL outside the rule, as a generated column or a functional key part; InnoDB allows such an index to be UNIQUE. Afterwards, in PostgreSQL, INSERT ... ON CONFLICT lets code insert-or-skip safely under concurrency.

On a large live table, how you build the index matters. A plain CREATE UNIQUE INDEX in PostgreSQL blocks writes until it finishes. CREATE UNIQUE INDEX CONCURRENTLY does not, but a duplicate arriving while it runs makes it fail and leave an invalid index that still enforces uniqueness; drop it and retry. The manual also warns that INSERT ... ON CONFLICT can fail unexpectedly while it runs. In MySQL, ALTER TABLE ... ADD UNIQUE INDEX with ALGORITHM=INPLACE, LOCK=NONE keeps writes flowing and stops with an error if the server cannot do it that way; a duplicate inserted during the build makes it fail at the end and roll back. So fence whatever creates the entity, re-run the duplicate count, merge, build the guard, re-run the count, and only then let the writers back.

The duplicate-merge outcome does this for one table on a restored copy you prepare: a merge script, an archive of every removed row, a log of every reference moved, a reverse script that is run, a reconciliation report and the guard migration in its non-blocking form. It never runs on your production database; you run the script after a restore point you have tested. It does not cover fuzzy matching, deduplication across systems or tables without discoverable references.

• Test that a duplicate insert is now rejected, with inserts running while the guard is built.
• Re-run the duplicate count immediately before the production run and stop if it has changed.