Hibernate throws DataIntegrityViolationException when trying to update object using merge method

Question

Previous update() method was throwing different object with the same identifier value was already associated with the session so I changed dao to merge. Now it gives org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

TeamDAOImpl

public void updateTeam(Team team) {
        Team teamToUpdate = getTeam(team.getId());
        teamToUpdate.setName(team.getName());
        teamToUpdate.setRating(team.getRating());
        Set<TeamMember> teamMember = team.getTeamMembers();
        teamToUpdate.setTeamMembers(teamMember);
        getCurrentSession().merge(teamToUpdate);
    }

Team entity

@Entity
@Table(name="teams")
public class Team {


    private Integer id;

    private String name;

    private Integer rating;

    private Set<TeamMember> teamMembers;

// ...
@ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinTable(name = "team_member", joinColumns =
    @JoinColumn(name = "FK_Team_id", referencedColumnName= "id"),
            inverseJoinColumns = @JoinColumn(name = "FK_Member_id", referencedColumnName = "id")
    )
    public Set<TeamMember> getTeamMembers() {
        return teamMembers;
    }

    public void setTeamMembers(Set<TeamMember> teamMembers) {
        this.teamMembers = teamMembers;
    }
}

Team mas ManyToMany relationship with Team members. On SQL level it's done using tables: teams, member, team_member.

What constraints are violated? How to fix it?

UPDATE:

CREATE TABLE `member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `teams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `rating` int(11) NOT NULL,
  `FK_Organization_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_Organization_id` (`FK_Organization_id`),
  CONSTRAINT `FK_Organization_id` FOREIGN KEY (`FK_Organization_id`) REFERENCES `organization` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `team_member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `FK_Member_id` int(11) NOT NULL,
  `FK_Team_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_Member_id` FOREIGN KEY (`id`) REFERENCES `member` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_Team_id` FOREIGN KEY (`id`) REFERENCES `teams` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Cannot add or update a child row: a foreign key constraint fails (db.team_member, CONSTRAINT FK_Member_id FOREIGN KEY (id) REFERENCES member (id) ON DELETE NO ACTION ON UPDATE NO ACTION)

Data:

enter image description here

UPDATE3

public Team getTeam(int id) {
        Team team = (Team) getCurrentSession().get(Team.class, id);
        return team;
    }

UPDATE4 P6Spy helped to discover error query:

insert into team_member (FK_Team_id, FK_Member_id) values (2, 2);

returning:

[SQL] insert into team_member (FK_Team_id, FK_Member_id) values (2, 2);
[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`db`.`team_member`, CONSTRAINT `FK_Member_id` FOREIGN KEY (`id`) REFERENCES `member` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

This may be an issue with your cascade. Just to test, try not setting the team members and try setting another field (that doesn't use cascade) to see what happens. — Ascalonian, Feb 20 '15 at 17:04
It works fine (updates all except teamMembers) if it's not set teamMembers in `TeamDAOImpl` — J.Olufsen, Feb 20 '15 at 17:29
Can you post the sample values you were providing while doing update() or merge..? — Amitesh Rai, Feb 20 '15 at 17:43
So you have `Team teamToUpdate = getTeam(team.getId());`, which essentially means `team` and `teamToUpdate` point to the same Team, correct? if so, try changing `Set teamMember = team.getTeamMembers();` to use `teamToUpdate.getTeamMembers();` — Ascalonian, Feb 20 '15 at 18:09
`teamToUpdate` is another object picking changes from passed `team`. No error and no update happening after suggested changes — J.Olufsen, Feb 20 '15 at 18:33
Nvm, I guess that was kind of a dumb suggestion, on my part haha. I essentially asked you to pull the members and just update them back. Sorry! I guess when you get the error you get, it usually means 2 objects have the same primary key. In this case, both `team` and `teamToUpdate` do and you're using both. So you just need to isolate those two more. With that said, would the `getTeamMembers()` be different for `team` and `teamToUpdate`? — Ascalonian, Feb 20 '15 at 18:39
Indeed `getTeamMembers()` on `team` and `teamToUpdate` would return different data — J.Olufsen, Feb 20 '15 at 19:32
If you're using `team.getid()` to pull a unique row from the database and setting it to `teamUpdate`, wouldn't they have the same though? — Ascalonian, Feb 20 '15 at 19:57

score 1 · Accepted Answer · answered Feb 20 '15 at 20:19

Problem was in CONSTRAINT definition in team_member table. Instead of:

CONSTRAINT `FK_Member_id` FOREIGN KEY (`id`) REFERENCES `member` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_Team_id` FOREIGN KEY (`id`) REFERENCES `teams` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

It has to be

CONSTRAINT `FK_Member_id` FOREIGN KEY (`FK_Member_id`) REFERENCES `member` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_Team_id` FOREIGN KEY (`FK_Team_id`) REFERENCES `teams` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

Hibernate throws DataIntegrityViolationException when trying to update object using merge method

1 Answers1