Soft delete pattern concept in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the soft delete pattern in databases?

Soft delete is a technique where instead of removing a record from the database, a flag (like a boolean column) is set to mark it as deleted. This way, the data stays but is treated as deleted.

beginner

Why use soft delete instead of hard delete?

Soft delete helps keep data for recovery, auditing, or historical reasons. It prevents accidental data loss by not physically removing records.

beginner

How do you usually implement soft delete in a table?

Add a column like `is_deleted` or `deleted_at`. When deleting, update this column instead of removing the row. Queries then filter out rows marked as deleted.

beginner

Write a simple SQL query to select only active (not deleted) records using soft delete.

SELECT * FROM table_name WHERE is_deleted = FALSE;

intermediate

What is a common column type used to mark soft deleted records with a timestamp?

A column like `deleted_at` with a DATETIME or TIMESTAMP type is used. If it is NULL, the record is active; if it has a date, it means when it was deleted.

What does the soft delete pattern do?

A Permanently removes records from the database

B Encrypts records for security

C Marks records as deleted without removing them

D Backs up records to another database

Soft delete marks records as deleted but keeps them in the database.

Which column is commonly used to implement soft delete?

A password

B user_id

C created_at

D is_deleted

The is_deleted column is often used as a flag for soft delete.

How do you exclude soft deleted records in a query?

A WHERE is_deleted = TRUE

B WHERE is_deleted = FALSE

C ORDER BY deleted_at DESC

D GROUP BY is_deleted

Filtering with WHERE is_deleted = FALSE excludes deleted records.

What is a benefit of soft delete?

A Allows data recovery after deletion

B Improves query speed by removing data

C Automatically encrypts data

D Prevents all data changes

Soft delete allows recovering data since it is not physically removed.

If a table uses a deleted_at column for soft delete, what does a NULL value mean?

A Record is active (not deleted)

B Record is deleted

C Record is archived

D Record is corrupted

NULL in deleted_at means the record is still active.

Explain the soft delete pattern and why it is useful in databases.

Think about how you keep old emails in a trash folder instead of deleting them permanently.

Describe how you would write a query to get only records that are not soft deleted.

Remember, soft deleted records are marked, not removed.