[SOLVED] Why does one delete statement cascade, but not the other?

Why does one delete statement cascade, but not the other?

In my Spring Boot app, I have a table message which has a child table message_recipient i.e. the latter holds a foreign key to the former. These are both mapped to domain classes using JPA.

I've defined the following Spring Data JPA repository

public interface MessageRepository extends JpaRepository<Message, UUID> {

    int deleteByIdAndStatus(UUID messageId, MessageStatus status);
}

If I call this method, the row in message and the associated child rows in message_recipient are deleted. However, if I define the JPQL statement myself

public interface MessageRepository extends JpaRepository<Message, UUID> {

    @Modifying
    @Query("""
        delete from Message m
        and m.id = :messageId
        and m.status = :status""")
    int deleteMessage(UUID messageId, MessageStatus status);
}

The deletion of the row in message fails because there are rows referencing it in message_recipient.

Why does the deletion cascade to child tables in the first case, but not in the second? Is there a way to make deleteMessage cascade without changing the table definitions (DDL)?

Solution

An explicitly declared query invokes a single SQL query, and there is no way to make a cascading deletion in SQL. Also, such query doesn't consider the entity lifecycle rules, like cascading operations.

More information about this difference in doc: https://docs.spring.io/spring-data/jpa/reference/jpa/query-methods.html#jpa.modifying-queries.derived-delete