JPA: DELETE WHERE does not delete children and throws an exception

Have you tried using session.delete(), or equivalent EntityManager.remove() ?

When you use an HQL delete statement to issue a query, you might be bypassing Hibernate's cascading mechanism. Take a look at this JIRA Issue: HHH-368

You will possibly be able to achieve this by:

Mother mother = session.load(Mother.class, id);
// If it is a lazy association, 
//it might be necessary to load it in order to cascade properly
mother.getChildren(); 
session.delete(mother);

I'm not sure right now if it is necessary to initialize the collection in order to make it cascade properly.

You could relay on the RDBMS to delete those Mothers using foreign key constraint. This assumes your generateing your DDL from entities:

@Entity
@Table(name = "CHILD")
public class Child  implements Serializable {

    @ManyToOne
    @JoinColumn(name = "MOTHER_ID", foreignKey = @ForeignKey(foreignKeyDefinition =
        "FOREIGN KEY(MOTHER_ID) REFERENCES MOTHER(ID) ON DELETE CASCADE",
        value = ConstraintMode.CONSTRAINT))
    private Mother mother;    
}

DELETE (and INSERT) do not cascade via relationships in JPQL query. This is clearly spelled in specification:

A delete operation only applies to entities of the specified class and its subclasses. It does not cascade to related entities.

Luckily persist and removal via entity manager do (when there is cascade attribute defined).

What you can do:

• fetch all Mother entity instances that should be removed.
• for each of them call EntityManager.remove().

Code is something like this:

String selectQuery = "SELECT m FROM Mother m WHERE some_condition";  
List<Mother> mothersToRemove = entityManager
    .createQuery(selectQuery)
    .getResultStream()
    .forEach(em::remove);