Hibernate duplicate entry with OneToMany relation

user_6811338 0
Viewed 351

For example I have a user entity in hibernate with these 2 fields: 

@Entity
@Table(name = "user")
public class User {
    @OneToMany(fetch = FetchType.LAZY)
    @JsonIgnore
    private Set<User> addedUsers;

    @OneToMany(fetch = FetchType.LAZY)
    @JsonIgnore
    private Set<User> wantToAdd;
}

addedUsers is for 2 people who agrees to match with each other, while wantToAdd is for an user who sends a friend request to another user but didn't receive a response yet.
Here's a series of queries I used: (assume there are 3 users a,b,c)

a add b -> good  
a add c -> good   
b add c -> failed

Now it gives "java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'UK_4l1fmdm138stci88r2ht2ggff'" error.
Table in MySQL workbench
It seems like want_to_match_id doesn't allow duplicate. How to fix this problem?

I also think it's bad for user reference to itself but I don't know how I could do otherwise.

spring-boot jpa hibernate database java
1 Answers

You are using the column want_to_match_id to match the user ids to the userids that are matched with that person. But you have added a unique key constraint on the column want_to_match_id which is causing the issue.When you add the unique key constraint to this column it means that you cannot match the same user id to multiple persons. Suppose i have a userid 1 for person A and have to match it for user id 2 and 3 of Person B and Person C . But i also want to match Person B with Person C , so the table entries will end up looking like (as per the details in question ):

user_id | want_to_match_id
1           2
1           3
2           3

But you have a unique key constraint on the column which won't allow this entry causing the issue. You should look at redesigning your table structure to better reflect your requirements.

user_78875160
Related