Unable to delete details records through Criteria API


April 2019


146 time


My web app is built on Spring, Spring Data, JPA (Hibernate), and MS SQL server. I have two classes/tables (Master and Detail). Here is how they are associated:


@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "master")
private Master master; 


@OneToMany(mappedBy = "master", fetch = FetchType.LAZY,cascade = {CascadeType.PERSIST, CascadeType.REMOVE, CascadeType.MERGE}, orphanRemoval = true)  
private List<Detail> details; 

I am able to delete a Master object (AND its associated Detail objects cascadingly) if I delete the Master object via regular Spring Data delete such as delete(id_of_Master_object).

Now I need to build a dynamic query to delete Master objects via Criteria API, I got error:

The DELETE statement conflicted with the REFERENCE constraint "FK_t7dg2y38t1ddm1lrq94cnpk3x". The conflict occurred in database "mydatabase", table "dbo.Detail", column 'master'. 

I understand the nature of this error. What should be the fix? Anything wrong in the above mapping?

I cannot rebuild the database. The app is already in production. I would like to avoid any changes (such as alter table...) to the database if I can.

2 answers


It's not a schema issue, it's that criteria delete does not cascade.

So you are left with 2 alternatives:

  1. query all masters you want to delete, and iteratively delete one-by-one taking advantage of cascade on details

  2. perform 2 criteria deletes: the first on details and the second on masters


(Posted on behalf of the question author).

For whoever coming to this thread, here is how my final solution is implemented.

  1. I followed "query all masters you want to delete" suggested by Michele. It is a collection.

  2. repository.delete(the_collection)