How to create UNIQUE column with JPA?

Question

My POJO is using JPA and when I apply unique value in the column is not working for me I tried to use the UniqueConstraint and also not working for me .

below is my code

@Entity
@Table(name = "users", uniqueConstraints={@UniqueConstraint(columnNames = {"user_id"}),@UniqueConstraint(columnNames = {"username"}),@UniqueConstraint(columnNames = {"email"})})

    public class Users {

     @Id
     @GeneratedValue(strategy = GenerationType.AUTO)
     @Column(name="user_id",unique=true,nullable = false)
     private int UserId;

     @Column(name = "username" ,unique=true,nullable = false)
     private String Username;

     @Column(name = "email",unique=true ,nullable = false)
     private String email;

     @Column(name = "firstname",nullable = false)
     private String firstname;

     @Column(name = "lastname", nullable = false)
     private String lastname;

     @Column(name = "password", nullable = false)
     private String password;

     @Column(name = "active")
     private int active;

     @ManyToMany(cascade=CascadeType.ALL)
     @JoinTable(name="user_role", joinColumns=@JoinColumn(name="user_id"), inverseJoinColumns=@JoinColumn(name="role_id"))
     private Set<Role> roles;

below is the generated table in the database (MySQL)

| users | CREATE TABLE users (
  user_id int(11) NOT NULL,
  username varchar(255) NOT NULL,
  active int(11) DEFAULT NULL,
  email varchar(255) NOT NULL,
  firstname varchar(255) NOT NULL,
  lastname varchar(255) NOT NULL,
  password varchar(255) NOT NULL,
  PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

Hibernate log after spring start

Hibernate: create table users (user_id integer not null, username varchar(255) not null, active integer, email varchar(255) not null, firstname varchar(255) not null, lastname varchar(255) not null, password varchar(255) not null, primary key (user_id)) engine=InnoDB Hibernate: alter table users drop index UKfnranlqhubvw04boopn028e6 Hibernate: alter table users add constraint UKfnranlqhubvw04boopn028e6 unique (username, email) Hibernate: alter table users drop index UK_r43af9ap4edm43mmtq01oddj6 Hibernate: alter table users add constraint UK_r43af9ap4edm43mmtq01oddj6 unique (username) Hibernate: alter table users drop index UK_6dotkott2kjsp8vw4d0m25fb7

Solution 1

Apparently there's nothing wrong with this code. unique=true in @Column is a shortcut for @UniqueConstraint(columnNames = {"user_id"} and other particular constraints. You could only use one of them except for multiple unique constraints.

But if you are confused why the generated CREATE TABLE doesn't contain these unique constraints, check the log, you can find alter table commands just after CREATE TABLE.

In this case commands could be,

alter table users add constraint UK_some_id unique (username)
alter table users add constraint UK_some_id unique (email)

Hope this help.

Solution 2

Try like this:

 @Entity
    @Table(name="users",uniqueConstraints=@UniqueConstraint(columnNames={"user_id","username","email"}))

        public class Users {

Solution 3

I had the same problem here, set length to your unique columns.. It worked here. Do

@Column(name = "userName", length = 50, unique = true)

Comments

[S.K.]

does the table exist before running the application? the unique property has effect only when the table doesn't exist and is created by the application.

[Osama Al-Banna]

@S.K. no it's fresh database ...JPA will create everything

[benjamin c]

@OsamaAl-Banna does your table created in db contain unique index constraints ?

[benjamin c]

@OsamaAl-Banna just to clarify, how did you check if unique constraints added to the table or not ?

[Osama Al-Banna]

@benjaminc my database is fresh without any tables ,I do if unique constraints are added by issue this command show create table users ,it will show how the table was created and I clearly can see unique feature are not exist in the table meta data.

[Osama Al-Banna]

I tried this approach already, but unfortunately it's not working .

[Osama Al-Banna]

Thanks Benjamin , I don't get exactly what you mean but I added Hibernate log during the table creation when spring boot start to the question.

[benjamin c]

@OsamaAl-Banna try removing uniqueConstraints of @Table

[Osama Al-Banna]

still the same , but I notice something the unique feature is now working in the JAVA object level,but in the database level there is unique feature ,but when I try to insert in the rest API it won't allow me .

[benjamin c]

@OsamaAl-Banna is unique keys are exist in db ? what happen when insert data ? any exception ?

[Osama Al-Banna]

unique key are not existed in the DB ...but the application won't allow me pass duplicate values ,but ...if insert values directly in the DB yes I can .but from the application side I can't .

[MWiesner]

The statements in this answer are misleading or incorrect.

[Osama Al-Banna]

i give up the only way is to added them unique constraint manually in the database using MySQL Workbench.