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

Question

I have the 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 doing a delete/remove 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`))

It seems there is an issue with the generated join table and foreign keys.

How can this be fixed so a Role can be deleted ?

---

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

Think how can JPA solve the many-to-many relationship.

I guess it creates table User, table Role and table user_role that contains references (foreign keys) to user and to role.

Now, if you want to remove role you have to remove all references of this role being held by users. In order to do this you have to iterate over all users that have such role and remove it from this user's role list. Then you can safely remove the role.

BTW once you solve this problem you will probably have the next one with Permission. So, if i were you I'd temporarily remove the permissions field from Role make the role deletion working and then restore the permissions to sole new problems if exist.