JPA: delete parent with related children

Question

I have following entities:

@Entity
@Table(name = "chat",
    uniqueConstraints = {
            @UniqueConstraint(columnNames = {"user_1", "user_2"})
    })
public class Chat {
    @ManyToOne
    @JoinColumn(name = "user_1")
    @OnDelete(action = OnDeleteAction.CASCADE)
    private User user1;

    @ManyToOne
    @JoinColumn(name = "user_2")
    @OnDelete(action = OnDeleteAction.CASCADE)
    private User user2;
}

User class:

@Entity
@Table(name="users",
  uniqueConstraints={
    @UniqueConstraint(columnNames={"company_id", "username"})
  }
 )
public class User {
    @Id
    @GenericGenerator(name = "uuid-gen", strategy = "uuid2")
    @GeneratedValue(generator = "uuid-gen",strategy=GenerationType.IDENTITY)
    private String id;

    // there is no field/reference to Chat entity
}

And User entity without any references to Chat entity. I need to remove user with it's chats. Problem is that user id (that I want to remove) could be either in user1 or user2 field. For example, I have user A and user B. They have chat C. And if I try to remove, for example, user A, it should remove user A and chat C. But with provided configuration, I have following error:

Cannot delete or update a parent row: a foreign key constraint fails 
(`mydb`.`chat`, CONSTRAINT `FKqslncg7pcc89gvjjpp9jypbha` 
FOREIGN KEY (`user_2`) REFERENCES `users` (`id`))

As possible solution I used this answer. But using of

entityManager.remove(user); 
entityManager.clear();

does not help. Also, I checked ddl code and there is no any mention of Cascade actions. How to fix this?

You mean when you delete the parent, all the related "dependent" entities must be deleted too, if that is the case, please refer to [this](https://stackoverflow.com/questions/18813341/what-is-the-difference-between-cascade-and-orphan-removal-from-db) using orphanRemoval — Mahmoud Al Siksek, Aug 03 '17 at 21:19
@MahmoudAlSiksek, this won't help. I use different annotations and I don't have references to children in parent class — Oleksandr H, Aug 07 '17 at 09:16

score 0 · Answer 1 · answered Aug 03 '17 at 21:23

I found a solution. The first thing that you need is to add the human-readable ForeignKey constraint and remove @OnDelete. Now my code looks like this:

@ManyToOne
@JoinColumn(name = "user_1", foreignKey = @ForeignKey(name = "user_1_fk"))
private User user1;

@ManyToOne
@JoinColumn(name = "user_2", foreignKey = @ForeignKey(name = "user_2_fk"))
private User user2;

Then I dropped this table, launched an application to allow hibernate re-create this table with proper FK names provided in annotations. Then I opened MySQL workbench and modified foreign keys for this table using following SQL:

ALTER TABLE chat DROP FOREIGN KEY `user_1_fk`;
ALTER TABLE chat DROP FOREIGN KEY `user_2_fk`;
ALTER TABLE chat
   ADD CONSTRAINT `user_1_fk`
   FOREIGN KEY (`user_1` )
   REFERENCES `users` (`id` )
   ON DELETE CASCADE;
ALTER TABLE chat
   ADD CONSTRAINT `user_2_fk`
   FOREIGN KEY (`user_2` )
   REFERENCES `users` (`id` )
   ON DELETE CASCADE;

That's all.

JPA: delete parent with related children

1 Answers1