0

I encounter the following problem with Hibernate and foreign keys:

When I first deploy my web application, Hibernate was configured with this parameters (among many others):

  • databasePlatform set to "generic" (not engine specific) dialect org.hibernate.dialect.MySQLDialect.
  • hibernate.hbm2ddl.auto set to update

As the default engine was MyISAM, Hibernate logically created MyISAM tables with indexes, ignoring creation foreign keys (since MyISAM doesn't support such constraints).

Now that I want to migrate every tables to InnoDB, I would like Hibernate to automatically create missing foreign keys. Unfortunately, it looks like Hibernate is just looking for the index :

  • If the index exists, Hibernate will not create the corresponding foreign key;
  • If I drop the index, Hibernate will create both index and foreign key.

Since I don't want to drop every index in my schema, do you know a way to tell Hibernate to create the foreign key even if the index is created?

Thank you.

ssssteffff
  • 964
  • 4
  • 16
  • 1
    Can you not just have hbm2ddl recreate the database from scratch? It is not designed for use in a production environment so I don't expect that Hibernate will be able to do that if it hasn't already on the update. – Tony Day Jan 03 '13 at 10:54
  • Actually I'd like to convert my testing environment database easily, in order to benchmark and test the resulting database. If everything works fine and the benchmark is conclusive, I will definitely not use hbm2ddl update in my production environment, but the adequat SQL script, tested in my staging environment. – ssssteffff Jan 03 '13 at 12:40

1 Answers1

0

My suggestion would be to allow hibernate to recreate the entire schema since this is your development database.

If however, you have lots of test data you don't want to lose then I would create a script using the available MySQL commands to automatically drop all of your indexes for Hibernate to recreate them. The commands you will need are:

show tables;

show index from `table_name`;

drop index `index_name` on `table_name`;

Organise these into some form of program script (shell, python or the application your building etc.) and you're good to go.

Tony Day
  • 2,170
  • 19
  • 25