CODEWITHSUNDEEP
jpaspring-dataspring-data-jpa
curious1
curious1

Reputation: 14727

Unable to delete details records through Criteria API

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:

Detail:

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "master")
private Master 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.

Upvotes: 1

Views: 412

Answers (2)

halfer
halfer

Reputation: 20437

(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)

Upvotes: 0

Michele Mariotti
Michele Mariotti

Reputation: 7459

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

Upvotes: 1

Related Questions