Hibernate Annotations - Case Insensitive UniqueConstraint
hibernate, postgresql
Solution
With PostgreSQL, you would indeed do something like this to implement your requirement:
CREATE UNIQUE INDEX My_Index on Component (lower(name));
But there is to my knowledge no way to achieve this using annotations.
The only option I can think of if you want to rely on Hibernate's `hbm2ddl` tool to generate the schema and still have that index created would be to leverage the `import.sql` feature. From the Rotterdam JBug and Hibernate's import.sql blog post:
import.sql: easily import data in your unit tests
Hibernate has a neat little feature that is heavily under-documented and unknown. You can execute an SQL script during the `SessionFactory` creation right after the database schema generation to import data in a fresh database. You just need to add a file named `import.sql` in your classpath root and set either `create` or `create-drop` as your `hibernate.hbm2ddl.auto` property.
I use it for Hibernate Search in Action now that I have started the query chapter. It initializes my database with a fresh set of data for my unit tests. JBoss Seam also uses it a lot in the various examples. `import.sql` is a very simple feature but is quite useful at time. Remember that the SQL might be dependent on your database (ah portability!).
#import.sql file
delete from PRODUCTS
insert into PRODUCTS (PROD_ID, ASIN, TITLE, PRICE, IMAGE_URL, DESCRIPTION) values ('1', '630522577X', 'My Fair Lady', 19.98, '630522577X.jpg', 'My Fair blah blah...');
insert into PRODUCTS (PROD_ID, ASIN, TITLE, PRICE, IMAGE_URL, DESCRIPTION) values ('2', 'B00003CXCD', 'Roman Holiday ', 12.98, 'B00003CXCD.jpg', 'We could argue that blah blah');
For more information about this feature, check Eyal's blog (Updated Location for dead link), he wrote a nice little entry about it. Remember if you want to add additional database objects (indexes, tables and so on), you can also use the auxiliary database objects feature.
Problem
I have an entity annotated with the following: ``` @Entity @Table(uniqueConstraints={@UniqueConstraint(columnNames={"name"})}) public class Component extends Model { ... } ``` Is it possible to make the UniqueConstraint case insensitive? We are using PostgreSQL.