@Column(unique=true) does not seem to work

Question

Even though I set the attribute to be @Column(unique=true), I still insert a duplicate entry.

@Entity
public class Customer {

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

    @Column(unique=true )
    private String name;

    ...
}

I set the name using regular EL in JSF. I did not create table using JPA

Answer 1

The unique=true element of the Column annotation and / or the UniqueConstraint annotation that can be used at the table level are used to specify that a unique constraint is to be included in the generated DDL.

In other words, they don't do anything during the runtime, the verification is left to the database (which makes sense as unicity can't be tested at the Java level reliably¹) and if for whatever reason you don't have the corresponding constraint(s) defined at the database level, nothing will happen.

Add the constraint manually:

ALTER TABLE Customer ADD CONSTRAINT customer_name_unq UNIQUE (name);

See also

• JPA 1.0 specification
  • 9.1.4 UniqueConstraint Annotation
  • 9.1.5 Column Annotation
• MySQL Documentation
  • 12.1.7. ALTER TABLE Syntax

_{¹ Unless you acquire a table lock (ouch!), you can't check for unicity with a SQL query in a concurrent environment.}

Answer 2

For future users stumbling on this issue. There are lots of great suggestions here; read through them as well as your error messages; they will be enough to resolve your problem.

A few things I picked up in my quest to get @Column(unique=true) working. In my case I had several issues (I was using Spring Boot, FYI). To name a couple:

• My application.properties was using spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect despite using MySQL 8. I fixed this with spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect. Check your version (I did this through the command line: mysql> STATUS).
• I had a User class annotated as an @entity which meant that JPA was trying to create a user table which is one of MySQL's (as well as postgres) reserved keywords. I fixed this with @Table(name = "users").

Answer 3

I did not create table using JPA

Then you should add the unique constraint to your table in your CREATE statement, for example, if you are using MySQL:

create Customer (id int primary key, name varchar(255) unique);

Answer 4

For InnoDB tables , there are limit for indexed columns. That means, you have to set max length for te field:

@Column(unique = true, length = 32)
private String name;

Answer 5

Try specifying the unique constraint at the class level using

@Table(uniqueConstraints={@UniqueConstraint(columnNames={"name"})})
public class Customer {
...
    private String name;
}

@Column(unique=true) doesn't work for me but when I used @UniqueConstraint at the class level, the column in the database (MySql) was appropriately set as unique.

I hope this helps someone else who may face this same issue in the future

Answer 6

I was also facing the similar issue but got it resolved.

• First, drop the table from Database

    drop table Customer;
  

• Stop your spring boot application and launch it again.

  This worked for me.

Answer 7

If table already existing on database and table existing non unique records, jpa dont set unique constraint to column. For solution:

1. clean duplicated records or delete all records.
2. then try to set unique constraint.

Answer 8

Make sure to delete the tables created by hibernate in your database. And then re-run your hibernate application again.

Answer 9

MySQL Hibernate Object Relational Mapping - Alter Contains after schema was generated.

1. In case you change your entity class [Customer] after the Hibernate generated the schemas, you can drop the schema and re-generate it. The constrains will apply.

2. Instead of dropping schemas you can manually alter the table

ALTER TABLE Customer ADD CONSTRAINT customer_name_unq UNIQUE (name);

Answer 10

It also happens when you have added the validation in the middle of the running application where all the databases and tables were already created.

As @Column is the persistence level annotation, it only works when the unique constraint is found in the required fields.

So just add all required annotations, delete all tables and then re-run the application. In this way the application will again create all the tables marked with @Entity annotation and also add 'unique' constraint with it.

It is important to note that all @Column annotations will have the same behaviour hence it is advised to add all required persistence level validations before head so that you don't need to delete tables again and again.