Naming strategy for database indexes

[thomasschuerger]

Normal indexes, unique indexes and foreign key indexes use a hash-based naming strategy with a type-specific prefix when no explicit index name is provided. This results in an index name like "IDXt5qbu8tpmk5um6bgw8fiv1n7b", where "IDX" is the prefix and the rest is the resulting hash value (which is an MD5 hash in BASE-35 from a generated string). Other prefixes are "FK_" for foreign keys and "UK_" for unique keys.

I have noticed (and verified it in the code in org.hibernate.boot.model.naming.NamingHelper) that the naming strategy used will sort the index columns alphabetically before the string is built where the hash is built from.

This means that creating an index on e.g. (col1,col2,col3) generates the same index name as an index that uses any permutation of these columns, even though these indexes are surely not identical. It also means that you cannot create two indexes using a column list which are permutations of each other (e.g. col1,col2,col3) and (col3,col1,col2) as both would have the same index name.

For example, an entity definition like

@Table(name = "accountlists", indexes = { @Index(columnList = "column1,column2"), @Index(columnList = "column2,column1") })

will only create the first index, because the second index would have the same index name and is therefore treated as already existing. However, there may be good reasons to create both indexes, depending on the queries run.

Is this an explicit design decision or a bug? Why are the columns sorted for the naming scheme instead of using their original order?

[beikov]

Is this an explicit design decision or a bug?

This seems like a bug to me. You should create a JIRA for this.

Creating some sort of naming strategy SPI for constraint names sounds like a good idea to me. Can you propose something more specific?