When developing business systems, you’ll almost certainly run into the requirement: “use logical deletion instead of physical deletion.” Writing WHERE deleted_at IS NULL in every query is not realistic.

This article organizes patterns for transparent logical deletion implementation using @SQLDelete + @SQLRestriction and @FilterDef, based on Spring Boot 3.x + Hibernate 6.4 + Lombok. We’ll also look at how to choose between these and @SoftDelete added in Hibernate 6.4, conflicts with unique constraints, restoration handling, recording the deleter, and other practical pitfalls.

Things to organize before choosing logical deletion

Logical deletion is a method of setting a “deleted flag” while keeping the actual data. It has advantages such as recovery from accidental deletion, consistency with audit logs, and protection of related data.

On the other hand, tables tend to grow large, it has poor compatibility with unique constraints, and performance can degrade if you get index design wrong. A realistic approach is to use it selectively—for example, keeping append-only tables like access logs as physical deletion.

For the column, deleted_at TIMESTAMP is recommended over deleted boolean. Since you can later trace “when it was deleted,” it’s useful for both auditing and restoration. With PostgreSQL, choosing TIMESTAMP WITH TIME ZONE will help you avoid timezone-related trouble.

Which annotation should you choose?

Quick comparison of 3 approaches

ApproachSupported VersionDeletion Timestamp ColumnDynamic ON/OFFNative QueryMain Use
@SQLDelete + @SQLRestrictionHibernate 6.3+◯ (deleted_at)× (always ON)Not appliedSolid choice for matching existing table designs
@SoftDeleteHibernate 6.4+× (boolean only)×Not appliedShortest path for new projects where boolean operation is acceptable
@FilterDef + @FilterAll Hibernate versions◯◯ (per Session)Not appliedDynamic switching for “view deleted list” on admin screens, etc.

Selection flow

  1. Do you want to keep the deletion timestamp? → If yes, @SQLDelete + @SQLRestriction is the first candidate. If no (boolean is sufficient), @SoftDelete is also a candidate.
  2. Do you want to display deleted items on the admin screen? → If yes, use @FilterDef together, or remove @SQLRestriction and fetch with native queries.
  3. Is the existing @Where being used? → It was deprecated in Hibernate 6.3. You can migrate by simply replacing it with @SQLRestriction.

In Hibernate 6, the options have increased. Let’s organize them first.

  • @Where was deprecated in Hibernate 6.3. The successor is @SQLRestriction, which has the same functionality but just with a different annotation name.
  • @SoftDelete is a dedicated annotation introduced in Hibernate 6.4, which auto-generates behavior based on boolean flags. If you can use boolean operation in a new project, it’s the shortest path. However, since it can’t keep the deletion timestamp, it’s not suitable when there are audit requirements.
  • In situations where you want to “keep the deletion timestamp” or “match existing table designs,” the combination of @SQLDelete + @SQLRestriction remains solid.

If you use @SoftDelete for a new project, you can write it as follows.

@Entity
@SoftDelete(columnName = "deleted", strategy = SoftDeleteType.DELETED)
public class Article { /* omitted */ }

Setting strategy to ACTIVE inverts the boolean, and you can customize the column name and values. If you want operation other than boolean, it’s easier to just choose @SQLDelete + @SQLRestriction. This article assumes existing projects, so we’ll focus on @SQLDelete + @SQLRestriction from here on.

Minimal entity example

Here’s the base User entity. We’re using Lombok’s @Getter/@Setter.

@Entity
@Table(name = "users")
@Getter
@Setter
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String email;

    @Column(nullable = false)
    private String name;

    @Column(name = "deleted_at")
    private LocalDateTime deletedAt;
}

Here’s the corresponding DDL (assuming PostgreSQL).

CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       VARCHAR(255) NOT NULL,
    name        VARCHAR(255) NOT NULL,
    deleted_at  TIMESTAMP WITH TIME ZONE NULL
);

Making it transparent with @SQLDelete + @SQLRestriction

@SQLDelete (org.hibernate.annotations.SQLDelete) is an annotation that replaces the DELETE statement issued by Hibernate with arbitrary SQL. @SQLRestriction (same package, formerly @Where) defines a WHERE clause that is always added during SELECT.

@Entity
@Table(name = "users")
@SQLDelete(sql = "UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = ?")
@SQLRestriction("deleted_at IS NULL")
public class User {
    // omitted
}

The important thing here is the specification that Hibernate automatically binds only the identifier (and the version if @Version exists) to the ? in @SQLDelete. You cannot inject arbitrary property values like deletedAt or deletedBy into ?. Either write literals (such as CURRENT_TIMESTAMP) directly in the SET clause, or switch to the @Modifying UPDATE pattern described later. When using composite keys or optimistic locking, the bind order and count change, so check the actual SQL with spring.jpa.show-sql=true before defining it.

The execution log of userRepository.deleteById(1L) will look like this.

update users set deleted_at = current_timestamp where id = ?
select u.id, u.email, u.name from users u where u.deleted_at is null and u.id = ?

@SQLRestriction is applied to JPQL and Criteria API, but not to native queries. This is an important property you can leverage when writing restoration queries.

If you also want to see deleted items, use @FilterDef together

Since @SQLRestriction is always ON and can’t be removed, you’ll have trouble when you want to view a “deleted list” on the admin screen. If you want to switch dynamically, use @FilterDef together.

@Entity
@Table(name = "users")
@SQLDelete(sql = "UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = ?")
@FilterDef(name = "activeOnly")
@Filter(name = "activeOnly", condition = "deleted_at IS NULL")
public class User { /* omitted */ }

The scope of filter activation is per Hibernate Session (EntityManager internally holds a Session). Generally, think of it as effective within the same transaction, or throughout an entire request if OSIV is enabled.

@Service
@RequiredArgsConstructor
public class UserQueryService {

    private final EntityManager em;
    private final UserRepository userRepository;

    public List<User> findActiveUsers() {
        em.unwrap(Session.class)
          .enableFilter("activeOnly");
        return userRepository.findAll();
    }

    public List<User> findAllIncludingDeleted() {
        em.unwrap(Session.class).disableFilter("activeOnly");
        return userRepository.findAll();
    }
}

When using a parameterized @FilterDef, set it with setParameter("name", value) on the return value of enableFilter. Note that @Filter, like @SQLRestriction, targets only JPQL/Criteria and is not applied to native queries. If you don’t have requirements for restoration or an admin screen, @SQLRestriction alone is sufficient.

Avoiding conflicts with unique constraints

The biggest pitfall with logical deletion is unique constraints. If email has UNIQUE attached, you can’t re-register with the same email address.

With PostgreSQL, a partial index is the most straightforward.

CREATE UNIQUE INDEX users_email_active_idx
    ON users (email)
    WHERE deleted_at IS NULL;

If you’re on PostgreSQL 15 or later, you can also choose NULLS NOT DISTINCT. Using this, in a composite UNIQUE of email + deleted_at, NULLs are also treated as duplicates, so you can narrow down to a single undeleted record.

MySQL doesn’t support partial indexes, but with version 8, you can achieve equivalent results using generated columns + UNIQUE.

ALTER TABLE users
    ADD email_active VARCHAR(255)
    GENERATED ALWAYS AS (IF(deleted_at IS NULL, email, NULL)) VIRTUAL,
    ADD UNIQUE KEY uk_users_email_active (email_active);

Since MySQL treats NULLs as “different” in UNIQUE constraints, setting email_active of deleted rows to NULL prevents them from being treated as duplicates.

Retrieving and restoring deleted data

When you want to see deleted items too, native queries—where @SQLRestriction doesn’t apply—are the shortest path.

public interface UserRepository extends JpaRepository<User, Long> {

    @Query(value = "SELECT * FROM users WHERE id = :id", nativeQuery = true)
    Optional<User> findByIdIncludingDeleted(@Param("id") Long id);

    Optional<User> findByEmailAndDeletedAtIsNull(String email);

    @Modifying
    @Query(value = "UPDATE users SET deleted_at = NULL WHERE id = :id", nativeQuery = true)
    int restore(@Param("id") Long id);
}

Before restoring, adding logic to check whether an active user with the same email exists using findByEmailAndDeletedAtIsNull can prevent unique constraint violations in advance.

If you also attach @SQLRestriction to the related entity of OneToMany, deleted items will be automatically excluded when fetching child collections from the parent entity. On the other hand, this means that the value of collection.size() will no longer match the physical record count. For aggregation or historical processing, it’s safer to recount using native queries.

When combining CascadeType.REMOVE on the parent with @SQLDelete, you also need @SQLDelete on the child side. If the child remains as physical deletion, the result will be inconsistent, so align the deletion strategy at the entity level. For relationships, also reading JPA entity relationship mapping and Spring Data JPA performance optimization for fetching will deepen your understanding.

Combining with Auditing to record the deleter

As mentioned earlier, since only the identifier is bound to ? in @SQLDelete, you’ll need other means if you want to record deleted_by (the deleter). The straightforward approach is to prepare a @Modifying UPDATE method in the repository that accepts the deleter as an argument.

public interface UserRepository extends JpaRepository<User, Long> {

    @Modifying
    @Query("UPDATE User u SET u.deletedAt = CURRENT_TIMESTAMP, u.deletedBy = :deletedBy WHERE u.id = :id")
    int softDeleteById(@Param("id") Long id, @Param("deletedBy") String deletedBy);
}

The deleter is retrieved from AuditorAware. To prepare for cases where there’s no authentication information in SecurityContext or where the user is anonymous, fixing the fallback to something like SYSTEM is safer.

@Service
@RequiredArgsConstructor
public class UserDeletionService {

    private final UserRepository userRepository;
    private final AuditorAware<String> auditorAware;

    @Transactional
    public void delete(Long id) {
        String deletedBy = auditorAware.getCurrentAuditor()
                .orElse("SYSTEM");
        userRepository.softDeleteById(id, deletedBy);
    }
}

With this pattern, you can use a two-tier approach: keep @SQLDelete on the entity, while executing logical deletion with the deleter via a service. For how to build AuditorAware and its relationship with @LastModifiedBy, refer to How to automatically manage created and modified dates with Spring Data JPA Auditing.

Verifying logical deletion with @DataJpaTest

Logical deletion has the behavior of “hiding when called” rather than “deleting when called,” so it’s worth confirming with tests whether it works as intended. @DataJpaTest wraps each test in a transaction and rolls back at the end, so you can write without worrying about data contamination between tests. By clearing the first-level cache with em.flush() and em.clear() before verification, you can confirm behavior at the actual SQL level. If you want to apply validation to the related email field, also refer to How to simply implement validation with Spring Boot @Valid annotation, and if you want to separate rules for registration/update, Group-based validation with Spring Boot @Validated annotation.

@DataJpaTest
class UserRepositorySoftDeleteTest {

    @Autowired UserRepository userRepository;
    @Autowired EntityManager em;

    @Test
    void findById_returns_empty_after_delete() {
        User saved = userRepository.save(newUser("[email protected]"));
        userRepository.deleteById(saved.getId());
        em.flush();
        em.clear();

        assertThat(userRepository.findById(saved.getId())).isEmpty();
        assertThat(userRepository.findByIdIncludingDeleted(saved.getId())).isPresent();
    }

    @Test
    void findById_works_again_after_restore() {
        User saved = userRepository.save(newUser("[email protected]"));
        userRepository.deleteById(saved.getId());
        em.flush();
        em.clear();

        int updated = userRepository.restore(saved.getId());
        em.flush();
        em.clear();

        assertThat(updated).isEqualTo(1);
        assertThat(userRepository.findById(saved.getId())).isPresent();
    }
}

Summary

Choosing how to do logical deletion isn’t that complex. For existing projects, make it transparent with @SQLDelete + @SQLRestriction, and add @FilterDef if restoration or admin screens are needed. If boolean column operation is acceptable for new projects, @SoftDelete is also an option. For unique constraints, partial indexes (or NULLS NOT DISTINCT) are the first choice for PostgreSQL, and generated columns + UNIQUE for MySQL. When you want to record the deleter, the realistic approach is not to rely on ? in @SQLDelete, but to handle it with @Modifying UPDATE and AuditorAware. Align the deletion strategy at the initial design stage.

Frequently Asked Questions (FAQ)

Q. Can @Where no longer be used?

It was deprecated in Hibernate 6.3, and the successor is @SQLRestriction. Since the functionality is the same and only the annotation name changed, you can migrate by simply replacing the import and annotation name.

Q. Which should I choose between @SoftDelete and @SQLDelete + @SQLRestriction?

If you want to keep the deletion timestamp (deleted_at) or match existing table designs, @SQLDelete + @SQLRestriction is the choice; if boolean flag operation is acceptable for a new project, @SoftDelete is the shortest path. If you need audit requirements or restoration timestamp tracking, choose the @SQLDelete family.

Q. Logical deletion conflicts with unique constraints (such as email). How do I avoid it?

For PostgreSQL, partial unique index with WHERE deleted_at IS NULL, or NULLS NOT DISTINCT in PostgreSQL 15+ are the first choices. For MySQL 8, you can achieve equivalent results with generated columns + UNIQUE.

Q. Can I inject deleted_by (the deleter) into the SQL of @SQLDelete?

No, you can’t. Only the identifier (and the version if @Version exists) is bound to ?. When you want to record the deleter, the realistic pattern is to prepare a @Modifying UPDATE in the repository and pass the deleter retrieved from AuditorAware as an argument.

Q. Does @SQLRestriction also apply to native queries?

No. Only JPQL / Criteria API are targeted. This is an important property when using native queries for restoration or retrieving deleted lists, and findByIdIncludingDeleted in this article also leverages this specification.