[Spring Boot] Many To One relationship continues after deleting the object

I'm developing a backend application with Spring Boot.
In this backend I have a Many To One relationship between two tables, the Organization table (One, Owner of the relationship) and the Asset table (Many).
Everything works fine, I can add new objects and they are related correctly.
But when I try to delete child objects (Asset) it just leaves the ID field of the Organization table as null and the Asset object continues to exist in the Database.

This is the part of the database where the tables are created:


Object Asset:


Object Organization:


Delete endpoint:


Database photo with object before deletion


Database photo with object after deletion


Before trying this way of deleting, I tried to remove the object from the organization object list, but I got a SQLGrammarException on the console.


Error:

You are not deleting it, you are just removing the reference to organization. That's probably not what you want.
Why not call delete method in assetRepository?

This is a JPA problem, not influenced by the fact that what you are doing in a webapp or Spring Boot, so I'm moving the question to our ORM forum.

Note to the easily-confused (e.g., me): @DeleteMapping has nothing to do with databases. It's an annotation that maps an HTTP DELETE verb to the annotated method (as opposed to a GET or POST).

In JPA, you cannot unilaterally connect and disconnect relationships reliably. If you're going to delete an Asset and it's related to other objects, then those other objects must be updated to remove references to the Asset Entity that you are deleting. And you must include the affected relating Entities in the collection of Entities to be updated in the database. Otherwise, yes, strange things can happen.

In the case of a parent/child with cascading, the cascade will delete ALL children when you delete the parent, but deleting a single child without updating the parent won't work.

Also note that in the event that you're doing lazy-fetch with detaching, you'll need to re-attach (merge) anything that wasn't fetched before detaching.

Tim Holloway wrote:Note to the easily-confused (e.g., me): @DeleteMapping has nothing to do with databases. It's an annotation that maps an HTTP DELETE verb to the annotated method (as opposed to a GET or POST).

In JPA, you cannot unilaterally connect and disconnect relationships reliably. If you're going to delete an Asset and it's related to other objects, then those other objects must be updated to remove references to the Asset Entity that you are deleting. And you must include the affected relating Entities in the collection of Entities to be updated in the database. Otherwise, yes, strange things can happen.

In the case of a parent/child with cascading, the cascade will delete ALL children when you delete the parent, but deleting a single child without updating the parent won't work.

Also note that in the event that you're doing lazy-fetch with detaching, you'll need to re-attach (merge) anything that wasn't fetched before detaching.

When I try to put a repository.delete I get a SQLGrammarException:

Exception: InvalidDataAccessResourceUsageException - could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Stack trace:

assetRepository.deleteById(asset.getId());

You can get a SQL dump to the logs. The application.yaml I use looks like this:

For application.properties that translates as the more verbose:


Except that you want to trace on org.hibernate.sql or something like that.

Tim Holloway wrote:You can get a SQL dump to the logs. The application.yaml I use looks like this:

For application.properties that translates as the more verbose:


Except that you want to trace on org.hibernate.sql or something like that.

Thank you very much!
Doing the logs I found that the error is not in the relationship between Organization and Asset, but in another relationship that involved the Asset, I fixed it and managed to delete correctly!

Matheus,
You may want to try CrudRepository or JpaRepository interface in Spring Data JPA to delete your Assets.
Reference:
https://www.baeldung.com/spring-data-jpa-delete