CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-designsql-delete
James Wierzba
James Wierzba

Reputation: 17548

How to implement soft delete in SQL on data with foreign key relationships?

I have a requirement to change the behavior of the "delete" function on a web application on a table that has 5 child foreign key relationships. It is a fairly large sized web application so I am looking for the least risky and lowest impact change so that it will require the minimum amount of changes to the web application.

I can think of two options:

  1. add a column to the table deletedwith default 0, and 1 will indicate that the record has been deleted. This will require updating all selects on the table (and also, on it's child tables, as they are now orphaned) to include the clause where deleted = 0. The web app is old and not well written (repeated code) so there are many places that will require this change, so it is risky that one or more locations might be missed.

  2. Move the record to another copy of the table, specifically for deleted records. I would likely need to mirror every child table as well.

Option 1 seems to be more effort up front, but more maintainable in the future, option 2 is not much work up front, minimal changes to web app, but very messy. Are there other options ?

Upvotes: 5

Views: 3844

Answers (1)

TommCatt
TommCatt

Reputation: 5636

This is an excellent example of why I am so fond of views.

  • Create the new column, Deleted
  • Rename the table: MyData => AllMyData
  • Create a view with the old name that exposes the data from the table exactly the way it looked before, but only for the "undeleted" rows.

Example:

create view MyData as
    select ...
    from   AllMyData
    where  Deleted = 0

  • Write triggers on the view to handle DML as if it was the original table.

  • Create another view to expose the deleted rows.

Example:

create view DeletedMyData as
    select ...
    from   AllMyData
    where  Deleted = 1

  • Implement a "soft delete" and "undelete" functionality within the app. This code will be the only code directly accessing the table, setting the value of Deleted to 0 or 1 as appropriate. Existing code will need no modification.

  • The child tables will probably require no changes at all. Since they are likely queried by joining with the undeleted view (the one with the original table name), records referring to "deleted" data will not be exposed but will be automatically re-exposed when data is undeleted.

  • UPDATE: The ON_DELETE trigger on the MyData view would just issue an update against the actual table, setting the Deleted value to 1. An UNDELETE action can be implemented using the ON_DELETE trigger on the DeletedMyData view to set the value to 0. So issuing a Delete on the MyData view would perform a soft delete (the record would vanish from the MyData data set and appear in DeletedMyData) and issuing a Delete on the DeletedMyData view would perform a soft undelete (the record would vanish from the DeletedMyData data set and reappear in MyData).

Upvotes: 4

Related Questions