CODEWITHSUNDEEP
jpacase-insensitive
manisha sharma
manisha sharma

Reputation: 29

How to add a case insensitive JPA unique constraint?

I want to add a case insensitive unique constraint to a JPA entity.

Assume we have an entity employee which needs to have unique constraint for two columns NAME and PROJECT_TITLE where NAME is case insensitive.

Insertion of JoHn, PROJECT1 should cause unique constraint violation when a row JOHN,PROJECT1 already exists in database as JOHN, JoHn are same in our case.

The SQL for the above requirement is given below

ALTER TABLE employee ADD CONSTRAINT employee_name_unique
    UNIQUE(LOWER(NAME),PROJECT_TITLE);

Upvotes: 2

Views: 6032

Answers (1)

Ondra Žižka
Ondra Žižka

Reputation: 46904

JPA doesn't support this kind of constraints. For a pure JPA solution, you'll have to use additional column. See the answer here: Case-insensitive JPA unique constraint?

@Entity Foo { 
    private value;
    @Column(unique = true)
    private valueLowercased;

    @PrePersist @PreUpdate private prepare(){
        this.valueLowercased = value == null ? null : value.toLowerCase();
    }
}

Upvotes: 6

Related Questions