How does soft delete work and what problems does it create in relational databases?

Soft delete involves abandoning the physical deletion of rows from tables using the DELETE command in favor of setting a special activity flag or timestamp using a regular update. Instead of the record disappearing from the database, the application starts filtering all queries by adding a mandatory status check condition. This approach allows you to preserve change history, restore objects accidentally deleted by the user, and ensure the integrity of related historical references.

However, the introduction of soft delete gives rise to a number of architectural problems that developers encounter sooner or later. The main difficulty lies in the need to constantly duplicate the condition for filtering deleted records in all SQL queries, views, and foreign keys. In addition, unique indexes begin to work incorrectly because deleted records continue to take up space and prevent the creation of new objects with the same unique attributes without complex composite indexes that take status into account.

To minimize the side effects of soft delete, it is recommended to apply the following architectural approaches.

Ultimately, soft delete is a compromise between the convenience of data restoration and the complication of database logic, requiring discipline from the entire development team.