Hibernate/Postgres: duplicate key value violates unique constraint

Question

I have problem with adding users role during registration request

This is my DB diagram:

And I have the following classes: first is entity role:

    package application.model;

    import lombok.*;
    import javax.validation.constraints.NotEmpty;
    import javax.validation.constraints.NotNull;
    import javax.persistence.*;
    import java.util.List;

    @NamedQueries({
            @NamedQuery(name = User.GET_USERS, query = User.QUERY_GET_USERS),
    })
    @Getter
    @Setter
    @NoArgsConstructor
    @Entity
    @Table(name = "users")
    public class User {
        public static final String GET_USERS = "User.get_users";
        public static final String QUERY_GET_USERS = "select u from User u";


        @Id
        @NotNull
        @GeneratedValue(strategy = GenerationType.SEQUENCE)
        @Column(name = "id")
        public int id;

        @NotNull
        @NotEmpty
        @Column(name="firstname")
        private String firstname;

        @NotNull
        @Column(name="lastname")
        private String lastname;

        @NotNull
        @Column(name="email")
        private String email;

        @NotNull
        @Column(name="password")
        private String password;

        @JoinTable
        @OneToMany
        private List<Role> roles;
    }

second entity is Role:

    package application.model;

    import lombok.Getter;
    import lombok.NoArgsConstructor;
    import lombok.Setter;
    import javax.persistence.*;
    import javax.validation.constraints.NotNull;

    @Getter
    @Setter
    @NoArgsConstructor
    @Entity
    @Table(name = "role")
    public class Role {
        @Id
        @NotNull
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "id")
        public int id;

        @NotNull
        @Column(name="name")
        private String name;

    }

So, i have 3 roles in my application, when I register new user with post request:

{
        "firstname": "imasdie5",
        "lastname": "nazasdwisko5",
        "email": "masdil",
        "password": "pass",
        "roles": [
             { "id":2 }
         ]
    }

First user is registered correctly, but when i send second request with same roles.id=2, i have:

ERROR: duplicate key value violates unique constraint "uk_d9najy24fium4vkivgwjuf0hw" Detail: Key (roles_id)=(2) already exists.

In dbeaver table users_role have constraint uk_d9najy24fium4vkivgwjuf0hw with type UNIQUE_KEY, so that's the problem, but how to change type to non-unique? Many users may have same role, so it is necessary for me

All tables are generated with hibernate. Table users_role is not entity in my application, maybe it should be entity? Do You have any advice what I should change to add one role for many users?

Alexey Filippov · Accepted Answer · 2020-02-12 14:45:48Z

1

Try to use @ManyToMany annotation

    @JoinTable
    @ManyToMany
    private List<Role> roles;

answered Feb 12, 2020 at 14:45

Alexey Filippov

2462 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mtmx Over a year ago

works, thanks :) i tried this few minutes ago, but i have hibernate.hbm2ddl.auto=update instead of create-drop

Collectives™ on Stack Overflow

Hibernate/Postgres: duplicate key value violates unique constraint

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related