Soft delete pattern concept in SQL - Step-by-Step Execution

Concept Flow - Soft delete pattern concept

Start: User wants to delete a record

↓

Update record's 'deleted' flag to TRUE

↓

Record remains in table but marked deleted

↓

Queries filter out records where 'deleted' is TRUE

↓

Deleted records hidden from normal views

↓

Optionally, restore by setting 'deleted' to FALSE

Soft delete means marking a record as deleted instead of removing it, so it stays in the table but is hidden from normal queries.

Execution Sample

SQL

UPDATE users
SET deleted = TRUE
WHERE id = 3;

SELECT * FROM users
WHERE deleted = FALSE;

Marks user with id=3 as deleted, then selects only users not marked deleted.

Execution Table

Step	Action	SQL Executed	Table State	Output
1	Mark user id=3 as deleted	UPDATE users SET deleted = TRUE WHERE id = 3;	User 3: deleted=TRUE; others deleted=FALSE	No output (update)
2	Select users not deleted	SELECT * FROM users WHERE deleted = FALSE;	Same as above	Returns all users except user 3
3	Attempt to select all users without filter	SELECT * FROM users;	Same as above	Returns all users including deleted ones
4	Restore user id=3	UPDATE users SET deleted = FALSE WHERE id = 3;	User 3: deleted=FALSE; others deleted=FALSE	No output (update)
5	Select users not deleted after restore	SELECT * FROM users WHERE deleted = FALSE;	Same as above	Returns all users including user 3

💡 Execution stops after restoring user and selecting active users.

Variable Tracker

Variable	Start	After Step 1	After Step 4	Final
deleted flag for user 3	FALSE	TRUE	FALSE	FALSE

Key Moments - 3 Insights

Why doesn't the DELETE statement remove the record?

Because soft delete uses UPDATE to set a 'deleted' flag instead of DELETE, so the record stays in the table but is marked as deleted (see execution_table step 1).

How do queries exclude deleted records?

By adding WHERE deleted = FALSE in SELECT queries, only records not marked deleted are returned (see execution_table step 2).

Can a deleted record be restored?

Yes, by updating the 'deleted' flag back to FALSE, the record becomes active again (see execution_table step 4).

Visual Quiz - 3 Questions

Look at the execution_table, what is the 'deleted' flag value for user 3 after step 1?

A TRUE

B FALSE

C NULL

D UNKNOWN

💡 Hint

Check the 'Table State' column in execution_table row for step 1.

At which step does the SELECT query return all users including the deleted one?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Look at the 'Output' column in execution_table rows for steps 2 and 3.

If we forget to add WHERE deleted = FALSE in SELECT, what happens?

A Query returns no records

B Deleted records are included in results

C Query fails with error

D Only deleted records are returned

💡 Hint

See execution_table step 3 where SELECT without filter returns all records.

Concept Snapshot

Soft delete means marking a record as deleted using a flag (e.g., deleted=TRUE) instead of removing it.
Use UPDATE to set the flag.
SELECT queries filter out deleted records with WHERE deleted=FALSE.
Deleted records remain in the table and can be restored by resetting the flag.
This preserves data and allows easy recovery.

Full Transcript

Soft delete pattern means when you want to delete a record, you don't remove it from the database. Instead, you mark it as deleted by setting a special flag, like a 'deleted' column to TRUE. This way, the record stays in the table but is hidden from normal queries. When you select data, you add a condition to only get records where deleted is FALSE. If you want to restore a deleted record, you just set the flag back to FALSE. This pattern helps keep data safe and recoverable. The example shows updating user id 3 to deleted=TRUE, then selecting only users not deleted, so user 3 is hidden. Later, user 3 is restored by setting deleted=FALSE again.

Practice

1. What is the main idea behind the soft delete pattern in databases?

easy

A. Marking data as deleted without actually removing it from the table

B. Permanently removing data from the database

C. Backing up data before deletion

D. Encrypting data before deletion

Solution

1. Step 1: Understand soft delete concept

   Soft delete means we do not remove data but mark it as deleted.

2. Step 2: Compare options

   Only Marking data as deleted without actually removing it from the table describes marking data instead of removing it.

3. Final Answer:

   Marking data as deleted without actually removing it from the table -> Option A

4. Quick Check:

   Soft delete = Mark data deleted, not remove [OK]

Hint: Soft delete means mark deleted, don't remove [OK]

Common Mistakes:

• Thinking soft delete removes data physically
• Confusing soft delete with backup
• Assuming encryption is part of soft delete

2. Which of the following SQL table columns is commonly used to implement a soft delete?

easy

A. deleted_at TIMESTAMP

B. is_deleted BOOLEAN

C. delete_flag VARCHAR(10)

D. remove_date DATE

Solution

1. Step 1: Identify common soft delete column

   The most common column is a boolean named is_deleted to mark true/false.

2. Step 2: Check other options

   Other columns can be used but is_deleted BOOLEAN is standard and simplest.

3. Final Answer:

   is_deleted BOOLEAN -> Option B

4. Quick Check:

   Soft delete uses boolean flag = is_deleted [OK]

Hint: Soft delete uses boolean flag like is_deleted [OK]

Common Mistakes:

• Using timestamp instead of boolean for soft delete
• Choosing vague column names
• Confusing soft delete with audit columns

3. Given the table users with a boolean column is_deleted, what will this query return?

SELECT * FROM users WHERE is_deleted = FALSE;

medium

A. Only users not marked as deleted

B. Only users marked as deleted

C. No users, syntax error

D. All users including deleted ones

Solution

1. Step 1: Understand the WHERE clause

   The query filters rows where is_deleted is FALSE, meaning not deleted.

2. Step 2: Determine returned rows

   Only users not marked deleted will be returned, excluding soft deleted rows.

3. Final Answer:

   Only users not marked as deleted -> Option A

4. Quick Check:

   WHERE is_deleted = FALSE returns active users [OK]

Hint: WHERE is_deleted = FALSE filters active rows [OK]

Common Mistakes:

• Thinking it returns deleted users
• Assuming syntax error due to boolean
• Confusing TRUE and FALSE values

4. You have this query to select active users:

SELECT * FROM users WHERE is_deleted = 0;

But it returns no rows even though some users are active. What is the likely problem?

medium

A. The table has no data

B. The is_deleted column is missing

C. The query syntax is invalid

D. The column is_deleted uses TRUE/FALSE, not 0/1

Solution

1. Step 1: Check data type of is_deleted

   If is_deleted is BOOLEAN, it expects TRUE/FALSE, not numeric 0/1.

2. Step 2: Understand why query returns no rows

   Using 0 may not match FALSE in some SQL dialects, causing no rows returned.

3. Final Answer:

   The column is_deleted uses TRUE/FALSE, not 0/1 -> Option D

4. Quick Check:

   Boolean columns need TRUE/FALSE, not 0/1 [OK]

Hint: Boolean needs TRUE/FALSE, not 0/1 [OK]

Common Mistakes:

• Assuming 0 equals FALSE in all SQL dialects
• Ignoring data type of column
• Thinking syntax error causes no rows

5. You want to implement soft delete on a products table. Which SQL statement correctly marks a product as deleted without removing it?

hard

A. INSERT INTO products (product_id, is_deleted) VALUES (10, TRUE);

B. DELETE FROM products WHERE product_id = 10;

C. UPDATE products SET is_deleted = TRUE WHERE product_id = 10;

D. SELECT * FROM products WHERE is_deleted = TRUE;

Solution

1. Step 1: Understand soft delete update

   Soft delete means updating a flag, not deleting the row.

2. Step 2: Analyze each option

   UPDATE products SET is_deleted = TRUE WHERE product_id = 10; updates is_deleted to TRUE for product 10, marking it deleted.

3. Final Answer:

   UPDATE products SET is_deleted = TRUE WHERE product_id = 10; -> Option C

4. Quick Check:

   Soft delete uses UPDATE to mark deleted [OK]

Hint: Use UPDATE to mark is_deleted TRUE [OK]

Common Mistakes:

• Using DELETE instead of UPDATE
• Trying to INSERT to mark deleted
• Using SELECT instead of modifying data