# Hibernate many-to-many cannot delete

**URL:** <https://discourse.hibernate.org/t/hibernate-many-to-many-cannot-delete/104>\
**Category:** Hibernate ORM\
**Created:** [January 22, 2018, 7:35am UTC](https://discourse.hibernate.org/t/hibernate-many-to-many-cannot-delete/104 "2018-01-22T07:35:48Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![546168949](https://avatars.discourse-cdn.com/v4/letter/5/3ab097/32.png) [@546168949](https://discourse.hibernate.org/u/546168949)\
**Post date:** [January 22, 2018, 7:35am UTC](https://discourse.hibernate.org/t/hibernate-many-to-many-cannot-delete/104/1 "2018-01-22T07:35:48Z")

</div>

I’m using hibernate.

```auto
@Entity
@Table(name="user")
public class User{
@ManyToMany(fetch = FetchType.LAZY)
  @JoinTable(name = "user_roles", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
  private List<Role> roles;
}

```

```auto
@Entity
@Table(name="role")
public class Role {
@ManyToMany(mappedBy = "roles", fetch = FetchType.LAZY)
  private List<User> users;
}

```

through repository.delete Role  
Cannot delete the association table relationship.Exception:  
Cannot delete or update a parent row: a foreign key constraint fails

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [January 22, 2018, 7:49am UTC](https://discourse.hibernate.org/t/hibernate-many-to-many-cannot-delete/104/2 "2018-01-22T07:49:39Z")

</div>

Try clearing the `users` collections before deleting:

```
role.getUsers().clear().
```

---

<div class="post-metadata">

**Author:** ![546168949](https://avatars.discourse-cdn.com/v4/letter/5/3ab097/32.png) [@546168949](https://discourse.hibernate.org/u/546168949)\
**Post date:** [January 22, 2018, 8:23am UTC](https://discourse.hibernate.org/t/hibernate-many-to-many-cannot-delete/104/3 "2018-01-22T08:23:29Z")

</div>

Still no, you need to set up cascade property？

---

<div class="post-metadata">

**Author:** ![546168949](https://avatars.discourse-cdn.com/v4/letter/5/3ab097/32.png) [@546168949](https://discourse.hibernate.org/u/546168949)\
**Post date:** [January 22, 2018, 8:24am UTC](https://discourse.hibernate.org/t/hibernate-many-to-many-cannot-delete/104/4 "2018-01-22T08:24:35Z")

</div>

I find that defining @query (“delete form where role\_name=:roleName”) in Repository can be deleted and generated:  
Hibernate: delete from user\_roles where (role\_id) in (select id from role where role\_name=?).  
I can’t understand.

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [January 22, 2018, 8:25am UTC](https://discourse.hibernate.org/t/hibernate-many-to-many-cannot-delete/104/5 "2018-01-22T08:25:22Z")

</div>

For many-to-many, `CascadeType.REMOVE` is a terrible idea.

For more details, check out [this article](https://vladmihalcea.com/a-beginners-guide-to-jpa-and-hibernate-cascade-types/).

---

<div class="post-metadata">

**Author:** ![546168949](https://avatars.discourse-cdn.com/v4/letter/5/3ab097/32.png) [@546168949](https://discourse.hibernate.org/u/546168949)\
**Post date:** [January 22, 2018, 8:27am UTC](https://discourse.hibernate.org/t/hibernate-many-to-many-cannot-delete/104/6 "2018-01-22T08:27:08Z")

</div>

I use @jointable at both ends. Do you recommend doing this?

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [January 22, 2018, 9:34am UTC](https://discourse.hibernate.org/t/hibernate-many-to-many-cannot-delete/104/7 "2018-01-22T09:34:22Z")

</div>

What you need to do is to:

1. Add the data access logic so we know what you are doing.
2. Activate the [SQL log](https://vladmihalcea.com/the-best-way-to-log-jdbc-statements/).
3. Add the stacktrace of the issue so we know exactly what fails. It might be due to some other entity.

> I use @jointable at both ends. Do you recommend doing this?

No, you don’t use that. You use `@JoinTable` on one end and `mappedBy` on the other.

> I find that defining @query (“delete form where role\_name=:roleName”) in Repository can be deleted and generated:  
> Hibernate: delete from user\_roles where (role\_id) in (select id from role where role\_name=?).  
> I can’t understand.

1. You have a typo `delete form`. Please copy-paste the exact code you have, don’t type it.
2. This is an SQL query. Did you really run it as such? Or are you running it like a JPQL query instead?
3. I can’t understand what you are doing either. Check the requirements I’ve given you above for what you need to do so that your question is crystal clear.

---

<div class="post-metadata">

**Author:** ![546168949](https://avatars.discourse-cdn.com/v4/letter/5/3ab097/32.png) [@546168949](https://discourse.hibernate.org/u/546168949)\
**Post date:** [January 22, 2018, 10:34am UTC](https://discourse.hibernate.org/t/hibernate-many-to-many-cannot-delete/104/8 "2018-01-22T10:34:32Z")

</div>

I expect that deleting the role will delete the association in the relational table.

But now there are exceptions.:

```auto
2018-01-22 17:50:40.785 WARN 18028 --- [io-18086-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1451, SQLState: 23000
2018-01-22 17:50:40.786 ERROR 18028 --- [io-18086-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper : Cannot delete or update a parent row: a foreign key constraint fails (`test`.`user_groups`, CONSTRAINT `FKcxjeeiqtt3g4v86stce81hpfh` FOREIGN KEY (`group_id`) REFERENCES `user_group` (`id`))
2018-01-22 17:50:40.788 INFO 18028 --- [io-18086-exec-8] o.h.e.j.b.internal.AbstractBatchImpl : HHH000010: On release of batch it still contained JDBC statements
2018-01-22 17:50:40.805 ERROR 18028 --- [io-18086-exec-8] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`user_groups`, CONSTRAINT `FKcxjeeiqtt3g4v86stce81hpfh` FOREIGN KEY (`group_id`) REFERENCES `user_group` (`id`))
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)

```

User Entity

```auto
@Setter
@Getter
@Entity
@Table(name = "user", uniqueConstraints = {@UniqueConstraint(columnNames = {"username"}),@UniqueConstraint(columnNames = {"email"})})
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE, region = "entityCache")
public class User {
  @Id
  @Column(name = "id", length = 36)
  private String id;
  @Temporal(TemporalType.TIMESTAMP)
  @Column(name = "created_time", nullable = false)
  private Date createdTime;
  @Column(name = "username", nullable = false)
  private String username;
  @Column(name = "enabled", nullable = false)
  private Boolean enabled;
  @Column(name = "email_verified", nullable = false)
  private Boolean emailVerified;
  @Column(name = "first_name")
  private String firstName;
  @Column(name = "last_name")
  private String lastName;
  @Column(name = "email")
  private String email;
  @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE, region = "entityCollectionCache") 
  @ElementCollection(targetClass = String.class)
  @CollectionTable(name = "user_actions", joinColumns = @JoinColumn(name = "action_id"))
  @Column(name = "action")
  private List<String> requiredActions;

  @ManyToMany(fetch = FetchType.LAZY)
  @JoinTable(name = "user_roles", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
  private List<ClientRole> roles;
  @ManyToMany(fetch = FetchType.LAZY)
  @JoinTable(name = "user_groups", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "group_id", referencedColumnName = "id"))
  private List<UserGroup> userGroups;
}

```

Role

```auto
@Setter
@Getter
@Entity
@Table(name = "client_role", uniqueConstraints = {@UniqueConstraint(columnNames = {"role_name","client_id"})})
@Cacheable
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_WRITE, region = "entityCache")
public class ClientRole {
  @Id
  @Column(name = "id", length = 128, nullable = false)
  private String id;
  @Column(name = "name", length = 255, nullable = false)
  private String name;
  @Column(name = "description", length = 2000)
  private String description;
  @ManyToMany(fetch = FetchType.LAZY)
  @JoinTable(name = "client_role_composites", joinColumns = {@JoinColumn(name = "role_id", referencedColumnName = "id")}, inverseJoinColumns = {@JoinColumn(name = "composite_role_id", referencedColumnName = "id")})
  private List<ClientRole> composites;
  @ManyToMany(mappedBy = "roles", fetch = FetchType.LAZY)
  private List<User> users;
}

```

remove method

```auto
@Transactional
  public void removeRole(String clientInfoId, String... roleName) {
    for(int i = 0; i< roleName.length; i++) {
      ClientRole role = repository.findByNameAndClientInfo(roleName[i], clientInfoId);
      roleRepository.delete(role.getId());
    }
  }

```

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [January 22, 2018, 11:01am UTC](https://discourse.hibernate.org/t/hibernate-many-to-many-cannot-delete/104/9 "2018-01-22T11:01:52Z")

</div>

> [@546168949](#):
>
> Cannot delete or update a parent row: a foreign key constraint fails (`test`.`user_groups`, CONSTRAINT `FKcxjeeiqtt3g4v86stce81hpfh` FOREIGN KEY (`group_id`) REFERENCES `user_group` (`id`))

That’s for the `user_groups` table. But since you haven’t activated the SQL log, as I told you, you don’t see the flow of SQL statements.

There’s something else in your code that you didn’t show us. Either you have a DB-level CASCADE on DELETE a FK-level or there is some other code trying to delete a `Group` entity as well.

---

<div class="post-metadata">

**Author:** ![546168949](https://avatars.discourse-cdn.com/v4/letter/5/3ab097/32.png) [@546168949](https://discourse.hibernate.org/u/546168949)\
**Post date:** [January 23, 2018, 2:10am UTC](https://discourse.hibernate.org/t/hibernate-many-to-many-cannot-delete/104/10 "2018-01-23T02:10:04Z")

</div>

I’m sorry:

I use Spring Boot Jpa auto config,spring boot version:1.5.9, hibernate-core :5.0.12

It cannot automatically delete the user\_roles relationship.

```auto
Hibernate: delete from role_composites where role_id=?
Hibernate: delete from client_role where id=?
2018-01-23 10:00:30.093 WARN 11132 --- [io-18086-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1451, SQLState: 23000
2018-01-23 10:00:30.094 ERROR 11132 --- [io-18086-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper : Cannot delete or update a parent row: a foreign key constraint fails (`test`.`user_roles`, CONSTRAINT `FKa8ud4r12oeoud33apgv613oqf` FOREIGN KEY (`role_id`) REFERENCES `client_role` (`id`))
2018-01-23 10:00:30.095 INFO 11132 --- [io-18086-exec-8] o.h.e.j.b.internal.AbstractBatchImpl : HHH000010: On release of batch it still contained JDBC statements
2018-01-23 10:00:30.150 ERROR 11132 --- [io-18086-exec-8] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`user_roles`, CONSTRAINT `FKa8ud4r12oeoud33apgv613oqf` FOREIGN KEY (`role_id`) REFERENCES `client_role` (`id`))
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
	at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeUpdate(FilterChainImpl.java:2843)

```

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [January 23, 2018, 6:57am UTC](https://discourse.hibernate.org/t/hibernate-many-to-many-cannot-delete/104/11 "2018-01-23T06:57:51Z")

</div>

I could replicate the issue and created this Jira ticket [HHH-12239](https://hibernate.atlassian.net/browse/HHH-12239).

Thanks for mentioning it.

---

<div class="post-metadata">

**Author:** ![546168949](https://avatars.discourse-cdn.com/v4/letter/5/3ab097/32.png) [@546168949](https://discourse.hibernate.org/u/546168949)\
**Post date:** [January 23, 2018, 9:02am UTC](https://discourse.hibernate.org/t/hibernate-many-to-many-cannot-delete/104/12 "2018-01-23T09:02:27Z")

</div>

Thank you. I will keep track of this issue.

---

<div class="post-metadata">

**Author:** ![dinesh\_reddy](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/dinesh_reddy/32/1973_2.png) [@dinesh\_reddy](https://discourse.hibernate.org/u/dinesh_reddy)\
**Post date:** [July 27, 2022, 7:37am UTC](https://discourse.hibernate.org/t/hibernate-many-to-many-cannot-delete/104/13 "2022-07-27T07:37:32Z")

</div>

I am facing the same issue, is this issue sorted?