Duplicates and Cascades

Article · 2:00

Finding duplicates that are already there, removing them, and what a cascade really deletes.

A unique constraint prevents duplicates from today. It does nothing about the ones already in the table, and adding the constraint fails until they are gone - which is the usual order of events on a real schema.

Finding them is a GROUP BY with a HAVING COUNT(*) > 1 on whatever combination should have been unique. Removing them means choosing which copy to keep, and the standard approach is a self-join that deletes every row with the same key but a higher id, keeping the earliest. Do it inside a transaction, and run the SELECT form first.

Cascades deserve care. ON DELETE CASCADE is right when the child cannot meaningfully exist without the parent - order lines without an order, phone numbers without a customer. It is wrong when the child is a record of something that happened: deleting a customer should not silently delete their invoices, because those are needed for accounting whatever happens to the account.

Cascades also chain. If orders cascade from customers and order items cascade from orders, deleting one customer removes rows from three tables. That is correct and it is also how a single DELETE turns into a very large transaction. Know the depth of your cascade before deleting a parent with a lot of children, and prefer a soft delete - a deleted_at column - where the data has to survive.

-- Find what should have been unique and is not.
SELECT email, COUNT(*) AS copies, MIN(id) AS keep_this
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

-- Remove them, keeping the earliest row. SELECT first, transaction always.
START TRANSACTION;

SELECT c.id, c.email              -- what WOULD be deleted
FROM customers c
JOIN customers keep
  ON keep.email = c.email AND keep.id < c.id;

DELETE c FROM customers c
JOIN customers keep
  ON keep.email = c.email AND keep.id < c.id;

SELECT ROW_COUNT();
-- COMMIT;

-- Only now will this succeed.
ALTER TABLE customers ADD UNIQUE KEY uq_customers_email (email);

-- Cascades chain. With orders cascading from customers and order_items
-- from orders, this one statement removes rows from three tables.
-- DELETE FROM customers WHERE id = 42;

-- Where the record must survive the account, do not cascade - mark it.
ALTER TABLE customers ADD COLUMN deleted_at DATETIME NULL;
UPDATE customers SET deleted_at = NOW() WHERE id = 42;