I do have the same problem as the OP, and I'm quite sure it's a common pitfall for many applications using different RDMS.
I first use the trick of defining both a foreign key and a index in the hibernate mapping. Thus in Oracle and such RDMS, the foreign key is created without an index, but then hibernate explicitly ask for an index creation and it's cool.
The main drawback is that on MySQL, you will have both an index explicitly created by the mapping, and one other automatically created by the RDMS during the foreign key creation.
So I named both foreign key and index with an overlapping name like in the following mapping:
<class table="document" name="xxx.model.Document">
<set cascade="all-delete-orphan" lazy="true" name="sentences" inverse="true">
<key column="idDocument" not-null="true" foreign-key="documentIdx" on-delete="cascade"/>
<class table="sentence" name="xxx.model.Sentence">
<many-to-one class="xxx.model.Document" cascade="save-update" name="document" column="idDocument" not-null="true" foreign-key="documentIdx" index="documentIdx"/>
It definitely works on Oracle (you have both index and FK), and in MySQL the index is only create once. Cool ? Not really because the explanation is quite ugly: The index is created first. Then the Foreign Key creation failed because an index with the same name as the FK alread exists in the database. Too bad it's not the other waw around (FK first, then index fails).
In the end you have a MySQL database quite speedy, but with not so much integrity checks :/
I finally found the solution, using "<database-object>" in the mapping. You could manually create an index after table creation (thus after FK creation!). But even better, you could define a dialect scope in which apply the database object. You just have to name the dialect that you have problem with, and you will have a perfectly working database at least !!
<create>CREATE INDEX documentIdx ON sentence(idDocument)</create>
<drop>DROP INDEX documentIdx ON sentence</drop>
I highly suggest someone write a definitive workaround for this problem and put it in the FAQ, in the documentation, or wherever it belongs to help other people who are getting stuck on this one. Because I hardly manage to find clues on the net about what I consider a basic and naïve topic.
Links that may help go further:
http://www.hibernate.org/hib_docs/nhibe ... ase-object