Please explain the difference (CASCADING DELETE not working as expected)

Hi,

I have 2 entities:

@Entity
@Table(name = "lessonInstance")
@Data
@NoArgsConstructor
@EqualsAndHashCode(callSuper = false)
public class LessonInstance extends SorEntity implements Serializable {
    @EmbeddedId
    private BaseIdentity baseIdentity;

    @Column(name = "name", columnDefinition = "VARCHAR(150)")
    private String name;

    @ToString.Exclude
    @OneToMany(targetEntity = LessonInstanceSchedule.class, cascade = {CascadeType.ALL})
    @JoinColumns({
            @JoinColumn(name = "schoolId", referencedColumnName = "schoolId"),
            @JoinColumn(name = "id", referencedColumnName = "id")
    })
    @JsonManagedReference
    private List<LessonInstanceSchedule> lessonInstanceScheduleList;

    ... and a lot of attributes...

}

and

LessonInstanceSchedule extends SorEntity implements Serializable {

    @EmbeddedId
    private ScheduleIdentity scheduleIdentity;

    @ToString.Exclude
    @ManyToOne(targetEntity = LessonInstance.class, fetch = FetchType.EAGER)
    @JoinColumns({
            @JoinColumn(name = "schoolId", referencedColumnName = "schoolId", insertable = false, updatable = false),
            @JoinColumn(name = "id", referencedColumnName = "id", insertable = false, updatable = false)
    })
    @JsonBackReference
    private LessonInstance lessonInstance;

    @Column(name = "startDate", columnDefinition = "DATE")
    private LocalDate startDate;

    @Column(name = "endDate", columnDefinition = "DATE")
    private LocalDate endDate;

    @Column(name = "startTime", columnDefinition = "TIME")
    private LocalTime startTime;

    @Column(name = "endTime", columnDefinition = "TIME")
    private LocalTime endTime;

    ... and whatever stuff it needs...
}

both composite IDs containing some (2, resp. 3) integers

Now there is the repo:

@Repository
public interface LessonInstanceRepo extends Repo<LessonInstance> {

    // pragmatic original approach (A)
    @Modifying
    @Query("delete from LessonInstance t where t.baseIdentity.schoolId = :schoolId")
    int deleteBySchoolId(@Param("schoolId") Integer schoolId);

    // recommended version by the book (B)
    long deleteByBaseIdentitySchoolId(@Param("schoolId") Integer schoolId);

   ...and whatever queries it needs...

}

Now the problem is:
I was trying to remove all “hand written” queries.

Both deletes seem to be identical, but version (B) seems not to fully recognize the @JoinColumns annotations, cause when calling it, I get

2024-07-01 20:40:01,392 [scheduling-1] WARN  o.h.e.j.s.SqlExceptionHelper SQL Error: 23502, SQLState: 23502 
2024-07-01 20:40:01,392 [scheduling-1] ERROR o.h.e.j.s.SqlExceptionHelper NULL nicht zulässig für Feld "SCHOOL_ID"
NULL not allowed for column "SCHOOL_ID"; SQL statement:
update lesson_instance_schedule set id=null, school_id=null where id=? and school_id=? [23502-224]

while Version (A) works fine.
So, spoken in DB language (which I must admit I feel much more comfortable with than I do with Hibernate) it occurs that a CASCADE SET NULL is executed in case (B), while the expected CASCADE DELETE is executed in Version (A).

I’d really appreciate if somebody would explain

  • what’s the reason of this different behaviour and
  • what would be the solution without @Query -

thx

PS. there are NO foreign keys defined on the database side

Hi, @Repository is not an Hibernate annotation. I think you should ask these kinds of questions to the Spring folks, as we do not have any control or visibility into what their query methods do internally.

I can say that for the first case, i.e. the one using @Query and JPQL, you’re running an Hibernate mutation query and that does not trigger cascade deletes - since we do not the identity of the affected entities (i.e. rows affected by the delete query), we’re not able to understand which associations we should cascade.

Hi, thanks for the comment, but, hm, my findings were just the opposite:
Both queries seem to recognize the 1:n relationship declared by@OneToMany and @JoinColumns.
The difference is that the @Modifying @Query obviously does a DELETE CASCADE, while the derived query performs a DELETE SET NULL (speaking in DB language), thus ignoring the updatable=false attribute, causing a crash on database level (PK, NOT NULL).
But you are right, I should ask the spring guys why that happens under their hood here.