How to delete a row having a foreign key in hibernate?

Question

I have two tables called users and roles. User can have only one role. What I want to do is to delete user by id. But I get following error:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`supermarket`.`users`, CONSTRAINT `FKcogjq1smjy03v5s2wfegritx6` FOREIGN KEY (`role_id`) REFERENCES `Roles` (`role_id`))

As I have understood, I delete row in user which has foreign key. But I couldn't figure out, how to delete row in roles table and only after that to delete record in users table. How to achieve this doing only one query?

Below is my classes:

@Entity
public class Users {
private int userId;
private String login;
private String password;
private Roles roles;

public Users(String login, String password, Roles roles) {
    this.login = login;
    this.password = password;
    this.roles = roles;
}

public Users() {
}


@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "role_id")
public Roles getRoles() {
    return roles;
}

public void setRoles(Roles roles) {
    this.roles = roles;
}

 //getters and setters

Roles:

@Entity
public class Roles {
private int roleId;
private String roleName;

@Id
@Column(name = "role_id", nullable = false)
public int getRoleId() {
    return roleId;
}

public void setRoleId(int roleId) {
    this.roleId = roleId;
}

@Basic
@Column(name = "role_name", nullable = true, length = 14)
public String getRoleName() {
    return roleName;
}

public void setRoleName(String roleName) {
    this.roleName = roleName;
}

}

Here is how I delete:

 public void deleteCasher(int id) {

    Session session = sessionFactory.openSession();
    Query query = session.createQuery("from Users where userId=:id")
            .setParameter("id", id);

    Users user = (Users) query.uniqueResult();

    session.beginTransaction();
    session.delete(user);
    session.getTransaction().commit();
    session.close();


}

So, what I am doing wrong? How to delete a user, also deleting role, making one query?

i think your sample code is wrong .this code will delete user correctly . — ali akbar azizkhani, Dec 11 '17 at 05:42
Thanks for reply, but my users table has foreign key. That's why I get exception. — neo, Dec 11 '17 at 06:32
Delete from users u where u.userid=1 , this query delete use rand execute correctly ,i dont know why you say get this exception ? I think when delete roles you will get this exception — ali akbar azizkhani, Dec 11 '17 at 17:18

score 2 · Accepted Answer · answered Dec 11 '17 at 08:05

If you want that behavior to be permanent, meaning every time a User is deleted, you could change the foreign key action now that the table has been created. This directly into MySQL, following this answer.

Change the constraint from ON DELETE RESTRICT to ON DELETE CASCADE

ALTER TABLE `users` DROP FOREIGN KEY `FKcogjq1smjy03v5s2wfegritx6`; 

ALTER TABLE `users` ADD CONSTRAINT `FKcogjq1smjy03v5s2wfegritx6`
  FOREIGN KEY (`role_id`) REFERENCES `Roles` (`role_id`)
  ON DELETE CASCADE;

score 1 · Answer 2 · answered Dec 11 '17 at 07:50

1

Possible Reasons--

1-- while creating user if you are creating roles in role table with respective userid

2-- if you are using userid in other table as FOREIGN KEY....

answered Dec 11 '17 at 07:50

Navnath Adsul

364
3
10

How to delete a row having a foreign key in hibernate?

2 Answers2