Combining delete-orphan with a where condition

Question

Hibernate mapping question where the behavior is ambiguous and/or dangerous. I have a one-to-many relationship that has a cascade-delete-orphan condition AND a where condition to limit the items in the collection. Mapping here -

<hibernate-mapping>
 <class name="User" table="user" > 

  <!-- properties and id ... -->

   <set table="email" inverse="true" cascade="all,delete-orphan" where="deleted!=true">
      <key column="user_id">
      <one-to-many class="Email"/>
   </set>

 </class>
</hibernate-mapping>

Now suppose that that I have a User object which is associated to one or more Email objects, at least one of which has a 'true' value for the deleted property. Which of the following two will happen when I call session.delete() on the User object?

1. The User and all the Email objects, including those with deleted=true, are deleted
2. The User and the Email objects that are deleted!=null are deleted.

On one hand, scenario 1) ignores the where condition, which may not be correct according to the domain model. BUT in scenario 2) if the parent is deleted, and there's a foreign key constraint on the child (email) table's join key, then the delete command will fail. Which happens and why? Is this just another example of how Hibernate's features can be ambiguous?

Answer 1

I didn't test the mapping but in my opinion, the correct (default) behavior should be to ignore the where condition and to delete all the child records (that's the only option to avoid FK constraints violations when deleting the parent). That's maybe not "correct" from a business point of view but the other option is not "correct" either as it just doesn't work.

To sum up, the mapping itself looks incoherent. You should either not cascade the delete operation (and handle the deletion of the child Email manually).

Or, and I think that this might be the most correct behavior, you should implement a soft delete of both the User and associated Email. Something like this:

<hibernate-mapping>
  <class name="User" table="user" where="deleted<>'1'"> 

    <!-- properties and id ... -->

    <set table="email" inverse="true" cascade="all,delete-orphan" where="deleted<>'1'">
      <key column="user_id">
      <one-to-many class="Email"/>
    </set>
    <sql-delete>UPDATE user SET deleted = '1' WHERE id = ?</sql-delete>
  </class>

  <class name="Email" table="email" where="deleted<>'1'"> 

    <!-- properties and id ... -->

    <sql-delete>UPDATE email SET deleted = '1' WHERE id = ?</sql-delete>
  </class>
</hibernate-mapping>

What is done here:

• We override the default delete using sql-delete to update a flag instead of a real delete (the soft delete).
• We filter the entities and the association(s) using the where to only fetch entities that haven't been soft deleted.

This is inspired by Soft deletes using Hibernate annotations. Not tested though.

References

• 5.1.3. Class
• 6.2. Collection mappings
• 16.3. Custom SQL for create, update and delete