case insensitive uniqueness constraint

[pdurbin]

Hi! My understanding from https://stackoverflow.com/questions/25743191/how-to-add-a-case-insensitive-jpa-unique-constraint is that it is not possible to add a case insensitive uniqueness constraint in JPA. Is this correct? If so, can it be supported some day? As a workaround we add constraints in SQL like this (we're using PostgreSQL

CREATE UNIQUE INDEX index_user_lower_email ON user (lower(email));

[Likqez]

any updates on this?

[gavinking]

Well there's no actual proposal here for how to do this.

Given the current design of the @Column and @UniqueConstraint annotations, I don't see any particularly obvious way to do this elegantly.

I suppose in principle we could introduce the notion of a "case-insensitive column" so you could write @Column(caseSensitive=false, unique=true) but that would go quite a lot beyond just unique key DDL generation, and should affect all other generated SQL for consistency.

But I don't really see why you need this: you can easily ask JPA to include your own SQL script as part of schema export, and write your unique constraint by hand.

[gavinking]

Anyway, unless there is a concrete proposal for how to do this in an elegant way, my recommendation would be to simply close this issue. It doesn't seem to fit well with the model, and is super easy to accomplish in a different way.

[pdurbin]

@gavinking hi! Thanks for getting back to me!

I like your "case-insensitive column" idea: @Column(caseSensitive=false, unique=true)

(Perhaps I should check with my team to see if they like it too but it's a long holiday weekend and I thought it would be better to give you feedback than risk immediate closure of this issue. Four of us were just at JavaOne a few weeks ago. We probably should have asked someone then!) 😄

I like how JPA abstracts away the details. Just add annotations and don't think too hard about what's going on under the covers.

Please forgive me, but I'm not sure I understand "you can easily ask JPA to include your own SQL script as part of schema export, and write your unique constraint by hand." If we are regularly exporting our schema, I'm not aware of it, but I'm not the strongest database person on our team. (We do import or apply database migrations all the time with Flyway.) I just checked back on the Stack Overflow question I mentioned above and I don't see anyone suggesting this export solution. Perhaps someone reading this could add this as an answer and it could be upvoted as the best answer.

Anyway, thanks again for the comments. I appreciate it. I'll show it to my team on Monday.

[pdurbin]

@gavinking the consensus so far is that we like your @Column(caseSensitive=false, unique=true) idea. Would more feedback from us be helpful? Thanks again for taking a look at this issue!

[gavinking]

So one thing I just noticed is that columnList member of the @Index annotation already accepts a SQL-like minilanguage. It's pretty reasonable to allow columnList="(lower(col))" and as of 6.3 Hibernate will accept that. Other implementations may already accept it, I'm not sure. And since an @Index can be declared with unique=true, that seems to solve the problem.

I doubt we need to require support for this, since only some databases allow it, but at least it's there as an option for implementations which want to support it.

[gavinking]

@pdurbin if this solution is acceptable, I'm inclined toward closing this issue.

Remember that everyone always has the fallback option of specifying constraints in the jakarta.persistence.schema-generation.create-script-source, and for stuff like this that seems super-appropriate to me.

[gavinking]

it is not possible to add a case insensitive uniqueness constraint in JPA

So, look, I'm just going to declare that this statement is false, and that it's indeed possible to add such a constraint in JPA, by writing down the constraint in a SQL script, and using jakarta.persistence.schema-generation.create-script-source to export it.

I don't think we should add official support for expressions in @UniqueConstraint or @Index, since that's functionality that's only supported on some databases. Sure, in principle, you could do it by adding a computed column to the table on MySQL or SQL Server, but then we would have to figure out how the name of such a column would be auto-generated and ... well, ugh, I don't see a good way to do that. And I don't think we need it in the spec.

So finally I don't think we should do anything specific here.