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`)
)

score 10 · Accepted Answer · answered Jun 03 '14 at 11:44

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.

Thank you for the suggestion. What do you think of having a bidirectional relationship ? This would make it easier to find parents to remove references to the instance to be deleted ? — James P., Jun 03 '14 at 22:34

score 4 · Answer 2 · answered Jun 03 '14 at 14:38

4

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

In this way, children entities don't have to be removed before parent, so you could delete a Role without deleting its Permissions before.

answered Jun 03 '14 at 14:38

Genzotto

1,954
6
26
45

Your answer was promising but the Exception is still thrown unfortunately. If I understand things correctly, `CascadeType.REMOVE` means that deletion of the parent also deletes children ? http://www.objectdb.com/java/jpa/persistence/delete – James P. Jun 03 '14 at 22:32
Yes, it does. If that didn't work, try to delete children objects before deleting the parent entity – Genzotto Jun 03 '14 at 22:40
I've added the exported SQL for `user_role`. – James P. Jun 03 '14 at 23:43
1

Update: I've added `@ManyToMany(mappedBy="roles") private List users = new ArrayList();` to Role. This seems to allow deletion of `Role`s while leaving `User`s intact. Now to find something that works for Permissions too. – James P. Jun 04 '14 at 01:20

score 2 · Answer 3 · answered May 25 '19 at 20:53

I got this one fixed by,

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

e.g: "admins" instead of "admin"

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

}

and by changing:

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

for:

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

in application.properties

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

3 Answers3