Hibernate deletes data in the wrong order - causes fk problems

[mystic]

This isn't a Spring problem, but I was thinking people on this forum could help out. It is hard to get a response on Hibernate's forums these days.

I have a database that is somewhat weird in that it eventually comes full circle. The domain model really needs to if I want to model it accurately. No way around it.

For example, one arm of the entity graph goes like this:

M->AM->AT->AQ->R->K

Another arm of the of the graph starting from entity 'M' goes like this:

M->T->Q->K

As you can see, both arms eventually point to K. What I want to do is delete an instance of M, and cascade delete everything on both arms. However, I want to delete all the instances in "M->AM->AT->AQ->R" part first, then go back and delete "M->T->Q->K". This would satisfy the foreign key constraints.

However, Hibernate favours going the opposite way... so when it finally arrives at K and tries to delete a K that is being used by an R, it bombs due to fk constraint.

I would appreciate help. I can only think of a few ideas:

1. Split K into two entities K1 and K2 to kill the eventual circular dependency. This will mess up many aspects of the system which are working just fine and as intended.

2. Use JDBC to delete the records manually, which is not something I really want to do if I can help it. There would be too many instances where this code needs to be run... such as deleting "T", "Q" and many other entities not listed.

3. Turn off foreign key constraints altogether. Wise?

Is there a simpler solution? Thanks

[gregd]

Have you considered using a manual flush in the hibernate session just after the first deletion? This overrides the write-behind behaviour that hibernate applies.

[mystic]

Yes. Hibernate will then delete the records in the correct order (I think) *but* it will then report an error saying there are 0 records and it expects "x" records to exist. So it bombs. It's own internal checking is actually wrong or something because 0 records is exactly what it should be.

If I turn off foreign key constraints, the deletions do in fact work. However, some records in the database still exist. For example, R->K is a Many-to-many relation. While Hibernate deletes all the R's that belong under that M, and it also deletes the K's, there are still a pile of R<->K records in that many-to-many join table left over.

It doesn't really make sense though. There are lots of other many-to-many collections in this project and they work fine. Hibernate seems to be crapping out and not understanding how to handle it. It's like it's drunk and it just does its best and gives up when 98% complete.

It just bothers me having these records left over in the database when they don't point to anything. I am pretty sure that it is an order problem as I look at the order it is deleting them in. If I selected the order to delete the records in, it would be very different... which suggests that it doesn't understand my model... either because I missed a small configuration option to help it out, or it's just not very good with this kind of model.