JPA @ManyToMany - Cannot delete or update parent row: foreign key constraint fails

Question

I have entities:

@Entity public class User { @ManyToMany(cascade=CascadeType.PERSIST, fetch=FetchType.EAGER) private List<Role> roles = new ArrayList<Role>(); 

---

 @Entity public class Role { @ManyToMany(cascade=CascadeType.PERSIST, fetch=FetchType.EAGER) private Set<Permission> permissions = new HashSet<Permission>(); 

When performing a delete / delete, the following exception is thrown:

 Caused by: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`user_role`, CONSTRAINT `FK_USER_ROLE_roles_ID` FOREIGN KEY (`roles_ID`) REFERENCES `role` (`ID`)) 

There seems to be a problem with the generated join table and foreign keys.

How can this be fixed so that the role can be removed?

---

Edit:

Exported SQL shows this:

 CREATE TABLE IF NOT EXISTS `user_role` ( `User_ID` bigint(20) NOT NULL, `roles_ID` bigint(20) NOT NULL, PRIMARY KEY (`User_ID`,`roles_ID`), KEY `FK_USER_ROLE_roles_ID` (`roles_ID`) ) 

Answer 1

Consider how the JPA will resolve many-to-many relationships.

I assume that it creates a User table, a Role table, and a user_role table that contains links (foreign keys) for the user and for the role.

Now, if you want to delete a role, you need to delete all the links of this role that are stored by users. To do this, you need to iterate over all users who have such a role and remove them from this list of user roles. Then you can safely remove the role.

By the way, as soon as you solve this problem, you will probably have the following with Permission . So, if I were you, I would temporarily remove the permissions field from Role to do the job with the role, and then restore permissions only for the only new problems, if they exist.

Answer 2

Try adding CascadeType.REMOVE to your mappings:

 @ManyToMany(cascade= {CascadeType.PERSIST, CascadeType.REMOVE}, fetch=FetchType.EAGER) private List<Role> roles = new ArrayList<Role>(); @ManyToMany(cascade= {CascadeType.PERSIST, CascadeType.REMOVE}, fetch=FetchType.EAGER) private Set<Permission> permissions = new HashSet<Permission>(); 

Thus, child objects should not be deleted before the parent, so you can delete the role without deleting its permissions earlier.

Answer 3

I fixed it

changing some table names (maybe these names were reserved words in MySQL?)

For example: "administrators" instead of "administrator"

 @Table(name = "admins") public class Admin extends TrackedEntity { } 

and by changing:

 spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect 

per:

 spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect 

in the application. properties