Hibernate many-to-many cannot delete

546168949 · January 22, 2018, 7:35am

I’m using hibernate.

@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;
}

@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

vlad · January 22, 2018, 7:49am

Try clearing the users collections before deleting:

role.getUsers().clear().

546168949 · January 22, 2018, 8:23am

Still no, you need to set up cascade property？

546168949 · January 22, 2018, 8:24am

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.

vlad · January 22, 2018, 8:25am

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

For more details, check out this article.

546168949 · January 22, 2018, 8:27am

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

vlad · January 22, 2018, 9:34am

What you need to do is to:

Add the data access logic so we know what you are doing.
Activate the SQL log.
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.

You have a typo delete form. Please copy-paste the exact code you have, don’t type it.
This is an SQL query. Did you really run it as such? Or are you running it like a JPQL query instead?
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.

546168949 · January 22, 2018, 10:34am

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

But now there are exceptions.:

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

@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

@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

@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());
    }
  }

vlad · January 22, 2018, 11:01am

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.

546168949 · January 23, 2018, 2:10am

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.

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)

vlad · January 23, 2018, 6:57am

I could replicate the issue and created this Jira ticket HHH-12239.

Thanks for mentioning it.

546168949 · January 23, 2018, 9:02am

Thank you. I will keep track of this issue.

dinesh_reddy · July 27, 2022, 7:37am

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

Topic		Replies	Views
Hibernate cannot delete Hibernate ORM	1	436	May 10, 2018
OneToMany CascadeType.REMOVE Hibernate ORM	3	261	January 23, 2024
Hibernate - How to remove entity from parent when deleting child Hibernate ORM	12	16501	January 29, 2019
Why does the JPA @ManyToMany association remove all entries and reinserts the remaining ones back upon modifications Hibernate ORM	7	15802	February 11, 2019
Generate query error table name not replaced by alias Hibernate ORM	1	427	March 27, 2023

Hibernate many-to-many cannot delete

Related topics