Hi, I am attaching a sample project to help explain the issue I’m having. test-hotel-delete.zip (97.6 KB)
There are 3 entities: Hotel, Room and Color.
The Hotel has a composition attribute of rooms (1 to many).
The Room entity has an inverse attribute of hotel. It is marked as delete cascade.
The Room entity also has an association attribute of colors (many to many).
All entities extend BaseUuidEntity with SoftDelete false.
I am unable to delete a Hotel. The framework attempts to delete the rooms in the composition (yay) but fails because of the colors link (boo).
The unexpected error is:
SQLIntegrityConstraintViolationException: integrity constraint violation: foreign key no action; FK_ROOCOL_ON_ROOM table: TESTHOTELDELETE_ROOM_COLOR_LINK
It seems to me that the links to the colors associated with the room being deleted should also be automatically deleted. Is this a bug? Is there a workaround?
For kicks, I created a Car entity with a many to many association to colors. Here I am able to delete a Car and any existing colors do not prevent it.
Thank you for the test project and clear explanation of the problem. Let me explain why it currently works this way.
When you delete a Car which has a collection of Colors, it is first merged into the persistence context on the middle tier (i.e. loaded an becomes managed). The colors attribute is also loaded and when the Car object is deleted, ORM unlinks the car from the colors because this attribute is an owning side of the association. So it happens on the ORM level - it executes deletions from the many-to-many link table.
For the Hotel entity, the many-to-many attribute is deeper in the graph, so when you delete Hotel, ORM loads it and its rooms collection, but not deeper. That’s why it tries to execute deletions for rooms but cannot do it because of the existing room-color links.
There is a workaround: to define the foreign key constraint for the room-color link with the on delete cascade clause. In your project, you can modify the constraint in two scripts:
for database initialization in modules/core/db/init/hsql/30.create-db.sql
for updating the current database in modules/core/db/update/hsql/18/181228-1-alterConstraint.sql
The script content is the same:
alter table TESTHOTELDELETE_ROOM_COLOR_LINK drop constraint FK_ROOCOL_ON_ROOM^
alter table TESTHOTELDELETE_ROOM_COLOR_LINK add constraint FK_ROOCOL_ON_ROOM
foreign key (ROOM_ID) references TESTHOTELDELETE_ROOM(ID) on delete cascade^
We will think what we can do to simplify things in this case, created issue.