Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What is your method of implementing soft deletes in a database?

Ideally the solution would allow for

  • decent performance on large tables
  • take relationships into account
  • take unique keys into account
  • the user should be able to find and restore his deleted items.

Thanks!

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

There's a couple of ways I can think of to do it:

  • You could have a "Deleted" indicator, which is set to "Y" when a record is deleted. Very simple, but it leaves deleted and active data in the same tables, which could cause performance problems if there's a lot of activity. Restoring a record is as simple as changing the indicator.

  • You could have a table that mirrors the structure of the one you want to delete from, and when you delete a record from the main table, insert that record into the "deleted_data" table. This allows you to move deleted data into separate table to give the main table better potential performance, but searching for data that is in either table could make things more complicated, and keeping the data structures in sync (when they change) is also a bit more work that has to be done. In this case, restoring the data would involve removing the record from the "deleted" table and inserting it back into the main table.

In both cases, you may want to find any child records and mark them as deleted as well. I suppose you could do this with triggers or with code.

share|improve this answer
    
When using the approach of a deleted flag, how do you prevent accidental access to the soft-deleted rows? –  Mark Stosberg Apr 16 '13 at 14:40
    
@MarkStosberg: One way would be that the application that reads the data could read from a view which excludes all soft-deleted rows. –  FrustratedWithFormsDesigner Apr 16 '13 at 15:31
add comment

For this example let's use a table that looks like this"

CREATE TABLE parent_data
(
    id int not null auto_increment,
    ...
    PRIMARY KEY (id)
);

If you want to do soft deletes, create table that hodls the id of the rows you want deleted. Maybe something like this:

CREATE TABLE deleted_parent_data SELECT id FROM parent_data WHERE 1=2;
ALTER TABLE deleted_parent_data ADD PRIMARY KEY (id);

Say you want to delete rows 10,20,30,40,50. Just run this:

INSERT IGNORE INTO deleted_parent_data VALUES (10),(20),(30),(40),(50);

You would always have to perform INNER JOINs to see valid data

SELECT A.* FROM parent_data A
INNER JOIN deleted_parent_data B
USING (id) WHERE B.id IS NULL;

Seeing deleted rows would be very fast

SELECT B.* FROM deleted_parent_data A
LEFT JOIN parent_data B
USING (id) WHERE B.id IS NOT NULL;

To restore row 20 in parent_data, just run

DELETE FROM deleted_parent_data WHERE id = 20;

To permanently delete the rows 20 and 50, you run two steps which you cannot rollback:

DELETE B.* FROM
(SELECT id FROM deleted_parent_data
WHERE id IN (20,50)) A
LEFT JOIN parent_data B
USING (id) WHERE B.id IS NOT NULL;
DELETE FROM deleted_parent_data WHERE id IN (20,50);

If you have child records in the table child_data, you will have to have a separate delete_child_data table and manage itin a similar way. Perhaps have the table look like this:

CREATE TABLE deleted_child_table SELECT parent_id,id WHERE 1=2;
ALTER TABLE deleted_child_table ADD PRIMARY KEY (id);
ALTER TABLE deleted_child_table ADD UNIQUE KEY (parent_id,id);

You could probably use triggers or FOREIGN KEY constraints between deleted_parent_table and deleted_child_table with all the ON DELETE CASCADE bells and whistles. That way, a permanent delete of row in deleted_parent_table should cascade to deleted_child_table.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.