How to check if object is referenced before deleting

Question

I have java application with web interface (Spring MVC) using Hibernate. I have quite simple DB scheme here with a Group entity which you can add/delete on one webpage. Then there is another Entity having Group as field therefor Entity has a FK to Group. Note that there might be generally many other entities referencing my Group.

If I want to delete Group object it might fail on FK constraint - the group is referenced by some other entity.

Is there a way how to perform a check wheter my Group is referenced and the delete will fail rather then performing the delete and catching the exception? What is the best practice here?

Thanks

NOTE: Just to be clear, simple select is not a solution since the Group might be referenced generaly by many other tables, not only one.

Answer 1

You could perform a SELECT COUNT on Entity based on the Group's ID and then only attempt a Group deletion if the returned value is 0.

What's wrong with surrounding your delete with try/catch?

Answer 2

Checking before deleting is less efficient, and might still lead to a constraint violation if some link is added by another transaction between the moment you check and the moment you delete.

On the other hand, it allows displaying a more specific error message to the user (like "the group is still referenced by a FooBar").

Just do what you feel is best in your specific case. Note however that if some exception happens, the whole transaction should be rolled back, and the session should be closed, because such an exception leaves the session in an inconsistent state. Moreover, it's not the delete, but the flush or the commit that will throw the exception.

Now, if you want to check, there is no magical solution. You might have bidirectional associations, and you could thus check that group.getUsers() is emtpy (although this will fetch all the users of the group). Or you could execute dedicated queries like select count(user.id) from User user where user.group = :group to know if the group is still referenced. But you need to do one of those for every possible reference to the group.