I cannot make MySQL column values unique, Spring Data JPA

Question

When I try to make a column value, unique = true I get the following error.

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "
    alter table student 
       add constraint UK_fe0i52si7ybu0wjedj6motiim unique (email)" via JDBC Statement
....
....
Caused by: java.sql.SQLSyntaxErrorException: BLOB/TEXT column 'email' used in key specification without a key length

This is my application properties file.

spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/dataJpaDB
spring.datasource.username=root
spring.datasource.password=1234
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL8Dialect
spring.jpa.properties.hibernate.format_sql=true
server.error.include-message=always

I'm using MySQL version 8.

This is my entity class

@Data
@Entity(name = "Student")
public class Student {

    @Id
    @SequenceGenerator(
            name = "student_sequence",
            sequenceName = "student_sequence",
            allocationSize = 1
    )
    @GeneratedValue(
            strategy = GenerationType.SEQUENCE,
            generator = "student_sequence"
    )
    @Column(
            name = "id",
            updatable = false
    )
    private Long id;

    @Column(
            name = "first_name",
            nullable = false,
            columnDefinition = "TEXT"
    )
    private String firstName;

    @Column(
            name = "last_name",
            nullable = false,
            columnDefinition = "TEXT"
    )
    private String lastName;

    @Column(
            name = "email",
            nullable = false,
            columnDefinition="TEXT",
            unique = true /// this is causing the problem
    )
    private String email;

    @Column(
            name = "age",
            nullable = false,
            columnDefinition = "INTEGER"
    )
    private Integer age;

    public Student(Long id, String firstName, String lastName, String email, Integer age) {
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
        this.age = age;
    }
}

When I remove the "unique = true" everything seems to work fine. What is causing this problem and how to resolve it.

P.S. sorry for the long post.

You are likely adding the "unique" key as a property of the field (as per your framework). You probably need to find how to add an "index" (of type unique) to a field, from your framework. I am assuming it would start with say "@index" instead of "@column". Your framework documentation (Spring) would have it prescribed. — nazim, May 28 '22 at 10:17
I'm following along a video tutorial for this and in the video, it worked smoothly, however they used PostgreSQL, does that make a different? — itsDanial, May 28 '22 at 13:06
Most likely, it shouldn't be a problem. Most frameworks use same syntax for different backend sql variations. — nazim, May 28 '22 at 13:29
See https://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length — Davide D'Alto, May 28 '22 at 18:06

Davide D'Alto · Answer 1 · 2022-05-28T19:16:38.017

When you specify that a field must be unique, Hibernate ORM creates a constraint on the database. But different databases might have different requirements about the creation of the constraint based on the column definition.

Checking the MySQL website about BLOB and Text types:

For indexes on BLOB and TEXT columns, you must specify an index prefix length.

The easiest solution is not to touch the columnDefinition attribute:

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

Not sure why you specify it, but you should only use that attribute if you want to define a column using a type that's different from the default used by Hibernate ORM. In this case the column will be a varchar(255) and everything will work fine.

If you want the column to be a TEXT and you still want to create the schema using Hibernate ORM, you could add the constraint creation in the import.sql (and remove the attribute unique=true). Hibernate ORM will execute the content of that file after the schema has been created and if you include the following query in it, it will create the constraint:

alter table Student add constraint my_unique_constraint unique (email(10))

I will let you to the Hibernate ORM guide for more details about schema generation

Keep in mind that this last solution is not portable and you will have to update the constraint creation query every time you change database (or the attribute name).

PS: I'm talking about Hibernate ORM in this answer but I should have used JPA or Spring Data. Anyway, everything still applies.

I cannot make MySQL column values unique, Spring Data JPA

1 Answers1