CODEWITHSUNDEEP
mysqlspringhibernate
neo
neo

Reputation: 1404

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

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?

Upvotes: 2

Views: 2268

Answers (2)

Déjà vu
Déjà vu

Reputation: 28850

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;

Upvotes: 2

Navnath Adsul
Navnath Adsul

Reputation: 364

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....

Upvotes: 1

Related Questions