DELETE Best Practices

Deleting data is one of the riskiest operations in SQL because it is often irreversible. Adopting safe habits can prevent costly mistakes and downtime.

Best Practices

Soft deletes: Instead of physically removing rows, add a deleted_at column and set it to the current timestamp. This preserves history and allows easy recovery.
Use transactions: Wrap DELETE in BEGIN/COMMIT with a ROLLBACK escape hatch.
Preview with SELECT: Always confirm affected rows before deleting.
Batch large deletes: Delete in chunks of 500–1000 rows to avoid long table locks.
Archive first: Copy rows to an archive table before deleting from the production table.

Implementing Soft Deletes

Soft deletes are a popular pattern in Laravel and other frameworks. By filtering WHERE deleted_at IS NULL in your queries, you effectively hide deleted rows without losing the underlying data. This is invaluable for auditing and data recovery.

Example

-- Soft delete: mark as deleted instead of removing
UPDATE users
SET deleted_at = NOW()
WHERE user_id = 99;

-- Hard delete in batches
DELETE FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 1000;

SQL

Full Editor

Pro Tip

Consider soft deletes for any table where history, auditing, or recovery might be needed.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned