Definition
- Soft delete -- never physically remove a row; mark it as deleted (boolean flag or timestamp) and filter it from normal queries.
- @SQLDelete -- rewrites the entity's DELETE into a custom SQL statement, typically
UPDATE ... SET deleted = true. - @SQLRestriction (Hibernate 6+) -- adds a static
WHERE deleted = falseto every SELECT for the entity, replacing the older@Where. - @Filter -- a dynamic, parameterized filter you enable per session; useful for admin/audit views that need to include deleted rows.
- @SoftDelete (Hibernate 6.4+) -- a single annotation that manages the deleted column, rewrites deletes, and filters selects automatically.
- Trade-offs -- unique constraints, table bloat, native query bypass, and cascading soft deletes require careful handling.
When National Bank of India "closes" an account, it does not shred the file -- regulations require keeping records for years. Instead, Rajesh stamps it "CLOSED" and moves it to an archive drawer that is skipped during normal daily work. @SQLDelete is the rule that "delete" means "stamp as closed." @SQLRestriction is the standing instruction "ignore anything stamped closed." @Filter is a switch the auditor can flip to say "today, include closed files too." @SoftDelete is a single policy that sets up all of this automatically.
Understanding the Concepts
What is soft delete?
Soft delete means you never physically remove a row. Instead you mark it as deleted with a flag or timestamp and filter it from normal queries. This preserves history, allows recovery, and satisfies audit and regulatory requirements.
-- Soft delete column
ALTER TABLE accounts ADD COLUMN deleted BOOLEAN DEFAULT false;
-- Hard delete: DELETE FROM accounts WHERE id = 'SAV2001'
-- Soft delete: UPDATE accounts SET deleted = true WHERE id = 'SAV2001'
Benefits of soft delete:
1. Data recovery -- undelete by setting deleted = false
2. Audit trail -- all records preserved for regulatory review
3. Referential integrity -- no broken foreign keys from deleted parents
4. History -- can analyze deleted records for trends
Drawbacks:
1. Table bloat -- deleted rows accumulate
2. Unique constraint complexity -- deleted rows still occupy the table
3. Every query must filter -- easy to forget in native SQL
How does @SQLDelete work?
@SQLDelete intercepts the DELETE that Hibernate would issue and replaces it with your custom SQL, typically an UPDATE that sets the deleted flag.
package com.bank.model;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import org.hibernate.annotations.SQLDelete;
import org.hibernate.annotations.SQLRestriction;
import java.math.BigDecimal;
@Entity
@Table(name = "accounts")
@SQLDelete(sql = "UPDATE accounts SET deleted = true WHERE id = ?")
@SQLRestriction("deleted = false")
public class Account {
@Id
private String id;
private String holderName;
private BigDecimal balance;
private boolean deleted = false;
}
package com.bank.service;
import com.bank.model.Account;
import com.bank.repository.AccountRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
public class SoftDeleteService {
private final AccountRepository accountRepo;
public SoftDeleteService(AccountRepository accountRepo) {
this.accountRepo = accountRepo;
}
@Transactional
public void closeAccount(String id) {
accountRepo.deleteById(id);
// Instead of: DELETE FROM accounts WHERE id = 'SAV2001'
// Executes: UPDATE accounts SET deleted = true WHERE id = 'SAV2001'
}
}
Expected SQL:
UPDATE accounts SET deleted = true WHERE id = 'SAV2001'
-- Row stays in the table, just marked as deleted
-- No physical deletion
How does @SQLRestriction filter deleted rows?
@SQLRestriction (or the older @Where) appends a static predicate to every SELECT for the entity. Normal finders, JPQL queries, and association loads automatically exclude deleted rows.
// With @SQLRestriction("deleted = false") on Account:
// findById: SELECT * FROM accounts WHERE id = ? AND deleted = false
// findAll: SELECT * FROM accounts WHERE deleted = false
// JPQL: SELECT a FROM Account a --> ... WHERE deleted = false
package com.bank.service;
import com.bank.model.Account;
import com.bank.repository.AccountRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
import java.util.Optional;
@Service
public class FilteredQueryService {
private final AccountRepository accountRepo;
public FilteredQueryService(AccountRepository accountRepo) {
this.accountRepo = accountRepo;
}
@Transactional(readOnly = true)
public List<Account> findAllActive() {
return accountRepo.findAll();
// SQL: SELECT * FROM accounts WHERE deleted = false
// Deleted accounts automatically excluded
}
@Transactional(readOnly = true)
public Optional<Account> findActive(String id) {
return accountRepo.findById(id);
// SQL: SELECT * FROM accounts WHERE id = ? AND deleted = false
// Returns empty if the account was soft-deleted
}
}
Expected behavior:
- All standard queries automatically add 'AND deleted = false'
- Soft-deleted accounts are invisible to normal operations
- No code changes needed in repositories or services
How does @Filter provide dynamic control?
@Filter is the flexible alternative that lets you decide at runtime whether to include deleted rows. Useful for admin views and audit screens.
package com.bank.model;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import org.hibernate.annotations.Filter;
import org.hibernate.annotations.FilterDef;
import org.hibernate.annotations.ParamDef;
import org.hibernate.annotations.SQLDelete;
import java.math.BigDecimal;
@Entity
@SQLDelete(sql = "UPDATE accounts SET deleted = true WHERE id = ?")
@FilterDef(name = "deletedFilter",
parameters = @ParamDef(name = "isDeleted", type = Boolean.class))
@Filter(name = "deletedFilter", condition = "deleted = :isDeleted")
public class FilterableAccount {
@Id
private String id;
private String holderName;
private BigDecimal balance;
private boolean deleted = false;
}
package com.bank.service;
import jakarta.persistence.EntityManager;
import org.hibernate.Session;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
public class AuditViewService {
private final EntityManager em;
public AuditViewService(EntityManager em) {
this.em = em;
}
@Transactional(readOnly = true)
public List<?> findActiveAccounts() {
Session session = em.unwrap(Session.class);
session.enableFilter("deletedFilter").setParameter("isDeleted", false);
return em.createQuery("SELECT a FROM FilterableAccount a").getResultList();
// Only active accounts returned
}
@Transactional(readOnly = true)
public List<?> findAllIncludingDeleted() {
// Filter NOT enabled -- all accounts returned, including deleted
return em.createQuery("SELECT a FROM FilterableAccount a").getResultList();
}
}
Expected behavior:
- With filter enabled (isDeleted=false): only active accounts
- Without filter: ALL accounts, including soft-deleted ones
- Auditors can see deleted records, normal users cannot
How does @SoftDelete work in Hibernate 6.4+?
@SoftDelete is the modern one-annotation approach. It manages the deleted column, rewrites deletes to updates, and filters selects automatically.
package com.bank.model;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import org.hibernate.annotations.SoftDelete;
import org.hibernate.annotations.SoftDeleteType;
import java.math.BigDecimal;
@Entity
@SoftDelete(columnName = "deleted", strategy = SoftDeleteType.DELETED)
public class ModernAccount {
@Id
private String id;
private String holderName;
private BigDecimal balance;
// No need for a 'deleted' field in the entity!
// Hibernate manages the column automatically
}
Expected behavior:
- delete() generates: UPDATE accounts SET deleted = true WHERE id = ?
- findAll() generates: SELECT * FROM accounts WHERE deleted = false
- No @SQLDelete or @SQLRestriction needed -- @SoftDelete does both
- The 'deleted' column is managed by Hibernate, not mapped as an entity field
What are the trade-offs of soft delete?
Soft delete introduces complexity around unique constraints, table bloat, native queries, and cascading.
| Trade-off | Problem | Solution |
|-----------|---------|----------|
| Unique constraints | Deleted rows block re-creation | Partial index: WHERE deleted = false |
| Table bloat | Millions of deleted rows | Periodic archival/purge to cold storage |
| Native queries | Bypass @SQLRestriction | Always add AND deleted = false manually |
| Cascade | Parent soft-deleted, children still active | Custom cascade logic for soft delete |
| Performance | Index includes deleted rows | Partial indexes on deleted = false |
-- Partial unique index (PostgreSQL): only active accounts must be unique
CREATE UNIQUE INDEX idx_account_email_active
ON accounts (email) WHERE deleted = false;
// WRONG: native query forgets the filter
@Query(value = "SELECT * FROM accounts WHERE balance > 0", nativeQuery = true)
List<Account> findRichAccounts();
// Returns deleted accounts too! @SQLRestriction bypassed!
// CORRECT: add the filter manually
@Query(value = "SELECT * FROM accounts WHERE balance > 0 AND deleted = false",
nativeQuery = true)
List<Account> findRichAccountsCorrect();
Trade-offs summary:
1. Unique constraints: need partial indexes
2. Table bloat: need periodic archival
3. Native queries: must manually filter
4. Cascade: must implement soft-delete cascade logic
5. Performance: partial indexes help
What are common mistakes with soft delete?
The most common mistake is forgetting to add the filter to native queries, which returns deleted rows. The second is not handling unique constraints for soft-deleted rows.
// WRONG: native query returns deleted rows
@Query(value = "SELECT COUNT(*) FROM accounts", nativeQuery = true)
long countAll(); // includes deleted accounts!
// WRONG: unique email constraint blocks recreating a deleted account
// Account with email 'priya@nbi.com' was soft-deleted
// New account with same email fails: duplicate key violation!
// CORRECT: partial unique index only on active rows
// CREATE UNIQUE INDEX idx_email_active ON accounts (email) WHERE deleted = false;
Common mistakes:
1. Native queries bypass @SQLRestriction -- forgotten filter
2. Unique constraints not accounting for deleted rows
3. No archival strategy -- table bloats indefinitely
4. Soft-deleting parent but not soft-deleting children
5. Using @Where (deprecated) instead of @SQLRestriction (Hibernate 6+)
What should a senior engineer know?
A senior engineer pairs soft delete with periodic archival, uses partial indexes for unique constraints, implements cascade soft delete through service-layer logic, and prefers @SoftDelete on Hibernate 6.4+ over the manual @SQLDelete + @SQLRestriction pairing.
Senior checklist:
1. @SoftDelete (6.4+) over manual @SQLDelete + @SQLRestriction
2. Partial unique indexes for soft-deleted tables
3. Periodic archival/purge of old deleted rows
4. Always add deleted filter to native queries
5. Implement cascade soft delete in the service layer
6. @Filter for dynamic audit/admin views
7. Test that deleted records are invisible in all query paths
Visual Overview
Soft Delete Annotation Stack: @SQLDelete + @SQLRestriction (manual): ┌──────────────────────────────────────────┐ │ delete(entity) │ │ │ │ │ ▼ │ │ @SQLDelete intercepts │ │ UPDATE accounts SET deleted=true WHERE id=?│ │ │ │ findAll() │ │ │ │ │ ▼ │ │ @SQLRestriction appends │ │ WHERE deleted = false │ └──────────────────────────────────────────┘ @SoftDelete (automatic, Hibernate 6.4+): ┌──────────────────────────────────────────┐ │ @SoftDelete(columnName="deleted") │ │ │ │ delete(entity) → UPDATE SET deleted=true │ │ findAll() → WHERE deleted=false │ │ Column managed automatically │ └──────────────────────────────────────────┘
@Filter (dynamic toggle):
Normal user request: Auditor request:
filter enabled filter NOT enabled
(isDeleted=false) (all rows visible)
│ │
▼ ▼
Active accounts only All accounts
(deleted rows hidden) (deleted rows visible)
Key Takeaway
Soft delete means you never physically remove a row; you mark it as deleted with a flag or timestamp and filter it from normal queries. In Hibernate, @SQLDelete intercepts the delete and replaces it with an UPDATE that sets the flag, and @SQLRestriction appends a static WHERE deleted = false to every query so deleted rows are invisible. @Filter provides a dynamic toggle that auditors can enable to see deleted records. And @SoftDelete in Hibernate 6.4+ combines all of this into a single annotation.
The trade-offs are real. Unique constraints need partial indexes because soft-deleted rows still occupy the table. Native queries bypass @SQLRestriction and can accidentally return deleted rows. Table bloat accumulates without periodic archival. And cascading soft delete requires service-layer logic because JPA cascade only applies to physical operations.
At National Bank of India, a closed account was once hard-deleted and was unavailable for a regulatory audit. Rajesh moved to soft delete so the record is preserved. Day-to-day screens never see closed accounts thanks to @SQLRestriction, while the audit view uses @Filter to include them. A partial unique index on the email column ensures re-creating a soft-deleted account does not violate the uniqueness constraint.
Remember one sentence: soft delete marks rows as deleted instead of removing them, @SQLDelete + @SQLRestriction implement it manually, @SoftDelete does it in one annotation, and always handle unique constraints and native queries carefully.
---