When you build business systems, you almost always run into the requirement “use soft deletes instead of physical deletes.” Writing WHERE deleted_at IS NULL into every single query is not realistic.

This article assumes Spring Boot 3.x + Hibernate 6.4 + Lombok and walks through transparent soft delete implementation patterns using @SQLDelete + @SQLRestriction and @FilterDef. We will also look at how to choose between these and the @SoftDelete annotation added in Hibernate 6.4, as well as the places where real projects tend to get stuck: conflicts with unique constraints, restore operations, and recording who performed the deletion.

Things to Sort Out Before Choosing Soft Deletes

A soft delete simply sets a “deleted” flag while leaving the actual data in place. Its advantages include recovery from accidental deletion, consistency with audit logs, and protection of related data.

On the other hand, tables tend to bloat, soft deletes interact poorly with unique constraints, and performance suffers if the index design is wrong. A practical approach is to keep physical deletes for append-only tables such as access logs and use soft deletes elsewhere.

For the column, deleted_at TIMESTAMP is recommended over deleted boolean. Being able to trace “when was this deleted” later is useful for both auditing and restoration. On PostgreSQL, choosing TIMESTAMP WITH TIME ZONE helps you avoid time zone-related trouble.

Which Annotation to Choose

Hibernate 6 expanded the options. Let’s sort them out first.

  • @Where became deprecated in Hibernate 6.3. Its successor is @SQLRestriction; the functionality is the same, only the annotation name changed.
  • @SoftDelete is a dedicated annotation introduced in Hibernate 6.4 that automatically generates behavior based on a boolean flag. If a new project can live with a boolean column, this is the shortest route. However, it cannot retain a deletion timestamp, so it is a poor fit when you have audit requirements.
  • For teams that “want to keep the deletion timestamp” or “need to match an existing table design,” the @SQLDelete + @SQLRestriction combination remains the solid choice.

Quick Comparison of the Three Approaches

ApproachSupported VersionsDeletion Timestamp ColumnDynamic ON/OFFNative QueriesMain Use Case
@SQLDelete + @SQLRestrictionHibernate 6.3+Yes (deleted_at)No (always ON)Not appliedSolid choice for teams matching an existing table design
@SoftDeleteHibernate 6.4+No (boolean-based)NoNot appliedShortest route for new projects where a boolean column is acceptable
@FilterDef + @FilterAll Hibernate versionsYesYes (per Session)Not appliedDynamic toggling, e.g. viewing a “deleted items list” in an admin screen

Decision Flow

  1. Do you want to keep the deletion timestamp? → If yes, @SQLDelete + @SQLRestriction is the first candidate. If no (a boolean is enough), @SoftDelete is also a candidate.
  2. Do you want to show deleted records in an admin screen too? → If yes, combine with @FilterDef, or drop @SQLRestriction and fetch them with native queries.
  3. Is the existing code using @Where? → It is deprecated in Hibernate 6.3. You can migrate simply by replacing it with @SQLRestriction.

If you use @SoftDelete in a new project, it looks like this:

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

Setting strategy to ACTIVE inverts the boolean, and the column name and values can also be customized. If you want anything other than a boolean setup, it is simpler to just go with @SQLDelete + @SQLRestriction. This article assumes an existing project, so from here on we focus on @SQLDelete + @SQLRestriction.

Minimal Entity Example

Here is the User entity that serves as our foundation. It uses 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;
}

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 Hibernate issues with arbitrary SQL. @SQLRestriction (same package, formerly @Where) defines a WHERE clause that is always appended on SELECT.

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

The important point here is that Hibernate automatically binds only the identifier (and the version, if @Version is present) to the ? in @SQLDelete. You cannot feed the value of an arbitrary property such as deletedAt or deletedBy into ?. Either write a literal (such as CURRENT_TIMESTAMP) directly in the SET clause, or switch to the @Modifying UPDATE pattern described later. Composite keys and optimistic locking change the bind order and count, so verify the actual SQL with spring.jpa.show-sql=true before defining it.

The execution log for userRepository.deleteById(1L) looks 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 applies to JPQL and the Criteria API, but not to native queries. This is an important property you can take advantage of when writing restore queries.

Combine with @FilterDef If You Also Need to See Deleted Records

Since @SQLRestriction is always ON and cannot be turned off, it gets in the way when you want to see a “deleted items list” in an admin screen. If you need dynamic switching, combine it with @FilterDef.

@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 { /* 省略 */ }

The scope in which a filter is enabled is the Hibernate Session (the EntityManager holds a Session internally). In practice, think of it as active within the same transaction, or across the 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 the parameter by calling setParameter("name", value) on the return value of enableFilter. Note that @Filter, like @SQLRestriction, applies only to JPQL/Criteria and not to native queries. If you have no restore or admin screen requirements, @SQLRestriction alone is enough.

Avoiding Conflicts with Unique Constraints

The most common pitfall with soft deletes is unique constraints. If email has a UNIQUE constraint, a user cannot re-register with the same email address.

On PostgreSQL, a partial index is the most straightforward solution.

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

On PostgreSQL 15 and later, NULLS NOT DISTINCT is also an option. With it, a composite UNIQUE on email + deleted_at treats NULLs as duplicates of each other, so you can limit non-deleted records to a single row.

MySQL does not support partial indexes, but on version 8 you can achieve the equivalent with a generated column + 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);

MySQL treats NULLs as “distinct” from each other in UNIQUE constraints, so setting email_active to NULL for deleted rows means they are not considered duplicates.

Fetching and Restoring Deleted Data

When you need to see deleted records, a native query, which @SQLRestriction does not affect, is 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 that checks with findByEmailAndDeletedAtIsNull whether an active user with the same email already exists lets you prevent unique constraint violations before they happen.

If you also put @SQLRestriction on the target of a OneToMany association, deleted records are automatically excluded when you fetch the child collection from the parent entity. On the other hand, this means the value of collection.size() no longer matches the physical record count. For aggregation and history processing, it is safer to re-fetch the count with a native query.

If you combine CascadeType.REMOVE on the parent with @SQLDelete, the child side also needs @SQLDelete. Leaving the child side as a physical delete produces a half-baked result, so align your deletion strategy per entity. For how to set up associations, see JPA entity relationship mapping, and for fetching concerns, reading Spring Data JPA performance optimization alongside this will deepen your understanding.

Recording the Deleter by Combining with Auditing

As mentioned earlier, only the identifier is bound to the ? in @SQLDelete, so if you want to keep deleted_by (who deleted the record), you need a different approach. The straightforward option is to provide a @Modifying UPDATE method in the repository that takes 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 obtained from AuditorAware. To handle cases where the SecurityContext has no authentication or the user is anonymous, it is safer to fix the fallback to something like SYSTEM.

@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 get a two-tier setup: keep @SQLDelete on the entity, while soft deletes that record the deleter go through the service. For how to build AuditorAware and how it relates to @LastModifiedBy, see How to automatically manage created and modified timestamps with Spring Data JPA Auditing.

Verifying Soft Deletes with @DataJpaTest

Soft deletes are not “call it and it disappears” but “call it and it becomes hidden,” so it is worth confirming in tests that they behave as intended. @DataJpaTest wraps each test in a transaction and rolls it back at the end, so you can write tests without worrying about data contamination between them. Clearing the first-level cache with em.flush() and em.clear() before asserting lets you verify the behavior at the actual SQL level. If you want to apply validation to the related email field, see How to implement validation simply with the Spring Boot @Valid annotation, and if you want separate rules for create and update, also refer to Group-based validation with the Spring Boot @Validated annotation.

@DataJpaTest
class UserRepositorySoftDeleteTest {

    @Autowired UserRepository userRepository;
    @Autowired EntityManager em;

    @Test
    void deleteすると findByIdは空になる() {
        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 restoreすると findByIdで再び取得できる() {
        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 a soft delete approach is not that complicated. For an existing project, make it transparent with @SQLDelete + @SQLRestriction, and add @FilterDef if you need restore functionality or an admin screen. For a new project where a boolean column is acceptable, @SoftDelete is also an option. For unique constraints, the first choice is a partial index (or NULLS NOT DISTINCT) on PostgreSQL and a generated column + UNIQUE on MySQL. When you want to keep the deleter, the realistic approach is not to rely on the ? in @SQLDelete but to handle it with a @Modifying UPDATE and AuditorAware. Align your deletion strategy at the initial design stage.