Hibernate Annotations - Case Insensitive UniqueConstraint

Question

Hi

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.

Thanks in advance

Rich

Answer 1

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, 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.