@OneToMany errors in MySQL: Cannot delete or update a parent row: a foreign key constraint fails

Question

I have an one-to-many relationship as follows

@Entity
@Table(name = "reminderheader")
public class ReminderHeader implements Serializable {

    @Id
    @org.hibernate.annotations.GenericGenerator(name = "REMINDER_HEADER_GEN", strategy = "native")
    @GeneratedValue(generator = "REMINDER_HEADER_GEN")
    @Column(name = "id", unique = true, nullable = false)
    @Basic(fetch = FetchType.EAGER)
    private long id;

    @OneToMany(fetch = FetchType.EAGER, orphanRemoval = true)
    @Cascade(value = { CascadeType.SAVE_UPDATE, CascadeType.DELETE })
    @JoinColumn(name = "HeaderID")
    @Fetch(FetchMode.SELECT)
    private Set<ReminderDetails> reminderDetailslist;
}

and

@Entity
@Table(name = "reminderdetails")
public class ReminderDetails implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, nullable = false)
    @Basic(fetch = FetchType.EAGER)
    private long id;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "HeaderID", nullable = false)
    private ReminderHeader reminderHeader;
}

When I want to delete ReminderHeader from the MySQL database as follows

String query = "delete ReminderHeader rHdr where rHdr.recipientGUID = :rHeaderGUID ";
Query myQry = getCurrentSession().createQuery(query);
myQry.setString("rHeaderGUID", RemHeaderGUID);
int deletedRow = myQry.executeUpdate();

then I get the following error

Cannot delete or update a parent row: a foreign key constraint fails

How can I delete ReminderHeader and its ReminderDetails children without getting this error?

---

Update: I changed the one-to-many relationship as follows:

@OneToMany(fetch = FetchType.EAGER, orphanRemoval = true, mappedBy = "reminderHeader")
@Cascade(value = { CascadeType.SAVE_UPDATE, CascadeType.DELETE })
@Fetch(FetchMode.SELECT)
private Set<ReminderDetails> reminderDetailslist;

and

@ManyToOne(fetch = FetchType.EAGER)
private ReminderHeader reminderHeader;

and I used the following method for deleting

String query = "From ReminderHeader rHdr where rHdr.recipientGUID = :rHeaderGUID ";
Query myQry = getCurrentSession().createQuery(query);
myQry.setString("rHeaderGUID", RemHeaderGUID);
List<ReminderHeader> remList = myQry.list();
for (int i = 0; i < remList.size(); i++) {
    getCurrentSession().delete(remList.get(i));
}

but it didn't change anything in the database and no error or exception appears.

Solution

You have two errors.

First error: you mapped the bidirectional association between header and details twice: once in the header, and once in the details. When you have a bidirectional association, one of the side (the one header side, in this case) must be declared as the inverse of the other side, using the mappedBy attribute:

@OneToMany(fetch = FetchType.EAGER, orphanRemoval = true, mappedBy = "reminderHeader")
@Cascade(value = { CascadeType.SAVE_UPDATE, CascadeType.DELETE })
@Fetch(FetchMode.SELECT)
private Set<ReminderDetails> reminderDetailslist;

Second error: the DELETE cascade is only applied when you use the Session.delete() method to delete the entity. Delete queries bypass the session entirely (meaning that the entities deleted by the query but previously loaded stay in the session, in the same state as if no query had been executed).

So, to cascade the deletion, you'll have to execute a select query to find all the headers to delete, and then loop over these headers and delete them using session.delete().

Comments

[JB Nizet]

If you have no changes in the database, it's probably that the query didn't return anything, or that a rollback occurred. Enter in debug mode, and check the generated SQL queries.

[learner]

@JBNizet, can you please take a look at my post stackoverflow.com/questions/32146911/…