CODEWITHSUNDEEP
javapostgresqlspring-boot
aLittleMind
aLittleMind

Reputation: 193

PSQLException: ERROR: duplicate key value violates unique constraint

I have two entities: User and Video.

User have such field:

@Column(name = "favourite")
@ElementCollection(targetClass = Video.class, fetch = FetchType.EAGER)
@CollectionTable(name = "favourite_videos", joinColumns = @JoinColumn(name = "user_id"))
private Set<Video> favourite;

A user can add a video to his collection. However, when another user tries to add a video that has already been added by another user, an error occurs:

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uk_4ai4388fdjwvno9jj7u9x1h9x".
Key (favourite_id)=(17) already exists.

In the database it's look like:

enter image description here

I mean that user with another id can't add videos with id's 17 and 18, or any other id of video, which has already been added by another user. The question is how to make sure that users can add videos that other users already have? I want the Video entity not to belong to just one user.

Upvotes: 0

Views: 1421

Answers (1)

prime
prime

Reputation: 15574

In your table favourite_videos the primary key is favourite_id. So in this case there cannot be another tuple where it has a favourite_id used in a previous tuple. (a tuple is one record (one row))

You can avoid this problem by making a composite primary key by making the combination of user_id and favourite_id as the primary key.

Upvotes: 2

Related Questions