Why am I getting a Unique index or primary key violation?

Question

I am trying to add a friendship relationship between two persons using Spring MVC. The first call goes well but the second one throws a Unique index or primary key violation?, why am i getting it?

@Entity
@Table(name="PERSON")
public class Person {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Long id;

    @ManyToMany(fetch = FetchType.EAGER)
    @ElementCollection
    private List<Person> friends;

    @ManyToMany(mappedBy="friends", fetch = FetchType.EAGER)
    @ElementCollection
    private List<Person> friendOf;


    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public List<Person> getFriends() {
        return friends;
    }

    public void setFriends(List<Person> friends) {
        this.friends = friends;
    }

    public List<Person> getFriendOf() {
        return friendOf;
    }

    public void setFriendOf(List<Person> friendOf) {
        this.friendOf = friendOf;
    }

    public void addFriend(Person person){
        if(this.getFriends() == null){
            this.friends = new ArrayList<>();
        }

        this.friends.add(person);
    }




  public void setFriendship(Long firstPersonId, Long scndPersonId){
        Person firstPerson = personService.getPerson(firstPersonId);
       firstPerson.addFriend(personService.getPerson(scndPersonId));
        personService.savePerson(firstPerson);  

        Person scndPerson = personService.getPerson(scndPersonId);
        scndPerson.addFriend(personService.getPerson(firstPersonId));
        personService.savePerson(scndPerson);
    }

Person pup1 = new Person();
Long pupId1 = controller.savePerson(pup1);
Person pup2 = new Person();
long pupId2 = controller.savePerson(pup2);
setFriendship(pupId1, pupId2);
Person pup3 = new Person();
long pupId3 = controller.savePerson(pup3)
controller.setFriendship(pupId3, pupId1); //This throws an exception
controller.setFriendship(pupId3, pupId2);

Why is the marked line is causing an exception? The first call to setFrienship between p1 and p2 succeddes, but the when i try to make a connection between p1 and p3 it fails with the following exception :

org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PUBLIC.UK_6VEJRSUXRONFC95I7O5XJNRMU_INDEX_D ON PUBLIC.PERSON_FRIENDS(FRIENDS_ID) VALUES 2

what is your person_friends table definition..? looks like the issue is with this table — Nuthan Kumar, Aug 19 '19 at 17:22
I am not using any table definition other than what is defined on the entity. the person_friends is probably auto generated by hibernate/spring. — TheDude, Aug 19 '19 at 17:25

score 1 · Answer 1 · answered Aug 19 '19 at 18:02

You need to specify join and inverse join columns directly. Please note that they are swapped on collections.

@ManyToMany
@JoinTable(name="friends",
 joinColumns=@JoinColumn(name="personId"),
 inverseJoinColumns=@JoinColumn(name="friendId")
)
private List<User> friends;

@ManyToMany
@JoinTable(name="friends",
 joinColumns=@JoinColumn(name="friendId"),
 inverseJoinColumns=@JoinColumn(name="personId")
)
private List<User> friendOf;

nsharma · Accepted Answer · 2019-08-19T18:21:03.943

Please have a look at this answer,i hope you will get it right. The more you needed to do was to specific the join columns

Hibernate recursive many-to-many association with the same entity

There could also be a possibility that you trying to persist the same entity twice,in your case pub1 . Trying using merge instead!

Your setFriendship method calls the your service to save the entity which you have already done once,this is the reason that it gives you unique key violation as the entity is already there.

Leave comments if you need more explanation!

Why am I getting a Unique index or primary key violation?

2 Answers2