We are using the @ForeignKey annotation together with @JoinColumn and @ManyToOne to enforce foreign key constraints.

Some target databases, such as MySQL, will automatically create an index to enforce the FK constraint (most/all FK constraints are single column).

Other databases, e.g. Oracle, do NOT automatically create a FK index.

Indexes on FK constraint columns are recommended (improves performance during deletes on Oracle), and we would like to ensure that such indexes are present on all database platforms.

One possible solution is to use the @Index annotation to explicitly create such an index. However, the disavantage to this approach is that MySQL (and maybe others) will then have TWO indexes on the same column - one automatically created by the FK constraint and one explicitly created.

Does anyone know a way to get one and only one index on the FK column(s) across all database platforms?

Thanks,

dan

I am running into the exact same issue, while supporting multiple databases. Can someone suggest the correct workaround for this problem.

Really appreciate the help.

Assuming you want to support H2, MySQL

Would it make sense to override the MySQL5InnoDBDialect.getAddForeignKeyConstraintString method and prevent creation of index here. So that you can use the @Index, @ForiegnKey annotations seamlessly for all databases to generate similar SQL. Appreciate inputs on this.

public String getAddForeignKeyConstraintString(
String constraintName,
String[] foreignKey,
String referencedTable,
String[] primaryKey, boolean referencesPrimaryKey
) {
String cols = StringHelper.join(", ", foreignKey);
return new StringBuffer(30)
.append(" add index ")
.append(constraintName)
.append(" (")
.append(cols)
.append("), add constraint ")
.append(constraintName)
.append(" foreign key (")
.append(cols)
.append(") references ")
.append(referencedTable)
.append(" (")
.append( StringHelper.join(", ", primaryKey) )
.append(')')
.toString();
}