Deleting a User's Data from MySQL: Dependency Order and Transactions

MySQL looks like Postgres until you start deleting rows across tables with foreign keys. Then the differences matter. This is how to delete one user's data from MySQL without breaking the app or locking the database into next week.

Step 1: Map the tables, then the constraints

Same starting point as anywhere: list the tables that hold this person's data, then list the foreign keys that point at your users table:

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
       REFERENCED_TABLE_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'users';

InnoDB enforces foreign keys, so the same dependency rule applies: delete children before parents. The difference is that MySQL does not support DEFERRABLE constraints. In Postgres you can defer a constraint check to the end of the transaction. In MySQL the check happens immediately on each statement, so your ordering has to be right the first time—there is no "fix it before commit" escape hatch.

Step 2: Do not reach for FOREIGN_KEY_CHECKS

When someone hits a constraint error, the common reflex is:

SET FOREIGN_KEY_CHECKS = 0;

Resist it. Disabling checks means you can delete a parent and leave orphans behind, silently. The orphaned rows are exactly the user's data you were trying to remove, now detached and forgotten—and they will resurface in a future export or breach report. Keep checks on and delete in the correct order instead.

If you genuinely need FOREIGN_KEY_CHECKS = 0 (for example, deleting from a large child table that cascades), re-enable it in a finally, verify the referential integrity afterward with a query that looks for orphans, and never let it ship in application code. It belongs in a controlled maintenance script, not in your deletion path.

Step 3: Delete children first, inside one transaction

MySQL has no deferrable constraints, but it does have transactions, and you should use them:

START TRANSACTION;

DELETE FROM user_sessions WHERE user_id = 42;
DELETE FROM orders WHERE user_id = 42;
DELETE FROM user_profiles WHERE user_id = 42;
DELETE FROM users WHERE id = 42;

COMMIT;

If a statement fails, ROLLBACK and the whole thing is undone. MySQL does not support ROLLBACK TO SAVEPOINT for data-definition statements, but you are only running DELETE here, so a plain transaction is enough.

Step 4: Watch InnoDB locking on big tables

Deleting a large chunk of rows in one statement takes a gap or next-key lock over the affected index range, which blocks writes to that range from other sessions. For a single user with thousands of events, batch the deletes:

DELETE FROM user_events
WHERE user_id = 42
LIMIT 5000;

Repeat until no rows remain. Batching keeps each statement short, so other traffic is not stuck waiting on one enormous lock. Make sure user_id is indexed—a full table scan with locks across the whole table is the worst case.

Step 5: Dry-run with counts, then run

Before any deletion, run the exact WHERE as a SELECT COUNT(*) and verify it matches your expectation for every table. The match clause is the whole game: if the identifier does not resolve for a table, the count is zero and the user's data quietly survives there.

An execution preview that counts before deleting is not optional nicety—it is how you catch a bad identifier before you realize, months later, that one table still holds the person's data. Erasure's MySQL connector follows this shape: you map the tables and identifiers in a Data Map, preview the row counts, then a durable job runs the parameterized deletes and records the outcome honestly.

Step 6: Caches, indexes, backups

The source database is only one home for the data. Caches, search indexes, and backups all hold copies. Backups are the messy one: a restore within the retention window brings deleted rows back. Decide your retention policy up front and accept the trade-off, or you will rediscover deleted data after a restore.

The safe order, in one line

Map every table, list the constraints, delete children before parents inside a transaction, never flip FOREIGN_KEY_CHECKS off casually, dry-run with counts, and handle the places the data survives. That is the whole method.

For the broader workflow, the deletion requests guide covers intake to evidence, the PostgreSQL guide covers the same problem in Postgres, and the data deletion hub collects it all.