Soft delete pattern concept in SQL

Introduction

Soft delete lets you hide data instead of removing it. This helps keep records safe and recoverable.

When you want to keep user accounts but mark them as inactive.

When deleting orders but still need to keep history for reports.

When you want to undo deletions if done by mistake.

When you need to keep audit trails for compliance.

When you want to avoid breaking links to deleted data.

Syntax

SQL

ALTER TABLE table_name ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;

-- To soft delete a row:
UPDATE table_name SET is_deleted = TRUE WHERE id = some_id;

-- To select only active rows:
SELECT * FROM table_name WHERE is_deleted = FALSE;

The is_deleted column marks if a row is deleted without removing it.

Queries should filter out rows where is_deleted is TRUE to hide deleted data.

Examples

Add a column to mark users as deleted or active.

SQL

ALTER TABLE users ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;

Soft delete the user with ID 5 by marking them as deleted.

SQL

UPDATE users SET is_deleted = TRUE WHERE user_id = 5;

Select only users who are not deleted.

SQL

SELECT * FROM users WHERE is_deleted = FALSE;

Sample Program

This example creates a products table with a soft delete column. It inserts three products, soft deletes one, and then selects only the active products.

SQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2),
  is_deleted BOOLEAN DEFAULT FALSE
);

INSERT INTO products (product_id, name, price) VALUES
(1, 'Pen', 1.20),
(2, 'Notebook', 2.50),
(3, 'Eraser', 0.80);

-- Soft delete product with product_id = 2
UPDATE products SET is_deleted = TRUE WHERE product_id = 2;

-- Select only active products
SELECT product_id, name, price FROM products WHERE is_deleted = FALSE ORDER BY product_id;

Important Notes

Soft delete keeps data safe but requires filtering in every query.

Remember to add is_deleted = FALSE in your SELECT queries to avoid showing deleted rows.

You can add an index on is_deleted for faster queries if the table is large.

Summary

Soft delete hides data by marking it instead of removing it.

Use a boolean column like is_deleted to track deleted rows.

Always filter queries to exclude soft deleted rows.