Hibernate version: 2.1.7c, 3.0.5

Hello!

We have an application that runs with MySQL and Oracle. The used database schema is created using hbm2ddl out of the various hbm.xml files.

The problem is that indexes on foreign key columns are only created automatically with MySQL, but not with Oracle as the target database. When explicitly defining an index on the table using "index=xxx" in the column description of the many-to-one field, it is also not quite satisfactory: An index is created with Oracle (good), but two indexes are created with MySQL (bad; the automatically created one and the additional one).

Can the automatic creation of indexes on foreign key columns be somehow switched on? It seems that this is already an older issue: http://forum.hibernate.org/viewtopic.php?t=924910

Thanks!
Christian

are you using the same dialect for both hbm2ddl generations?

Hi!

You mean for Oracle and MySQL? No. With version 2.1.7c: for MySQL, I use net.sf.hibernate.dialect.MySQLDialect, and for Oracle, I use net.sf.hibernate.dialect.OracleDialect. For 3.0.5, I use org.hibernate.dialect.MySQLInnoDBDialect and oracle.jdbc.driver.OracleDriver, respectively.

But I'm not sure if I understood your question correctly.

Christian

oracle.jdbc.driver.OracleDriver is not a dialect . you might want to look at extending the Oracle dialect and overriding the methods related to DDL generation.

Oops, copy/paste error. I of course meant org.hibernate.dialect.OracleDialect.

But the basic problem remains: Using the MySQL dialects, the indexes on foreign key columns aren created automatically, while this is not done with the Oracle dialects. Or does this happen automatically with your configuration?

Christian

If you would like to change the DDL generation of MySQL, extend the MySQL dialect and do some experimenting.

Maybe I made my point a bit unclear: I do like the behaviour of MySQL dialect (automatic index creation on foreign keys), but I would like to have that also with the Oracle dialect.

Of course, I can extend the Oracle dialect and add the correct 'create index' statement into the getAddForeignKeyConstraintString method like in the MySQL dialect.

But I wonder whether this problem occurs only to me (then extending the dialect class is indeed the correct solution), or whether this should be solved in general. And when you consider the old post (see my initial posting), both Steve and Gavin seemed to agree that automatic creation of the index on a foreign key column is desirable. On the other hand, the posting was done two years ago, so maybe there is indeed no common need for this?

Christian

so ... make it happen and submit a patch.

Read this to see why you need to create indexes for foreign keys for Oracle:

http://asktom.oracle.com/~tkyte/unindex/index.html

and:

http://asktom.oracle.com/pls/ask/f?p=49 ... 2016138754

Funny how that's not the default behaviour of the schema export code.

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:



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 !!



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
http://jira.nhibernate.org/browse/NH-1058

Thanks for writeup - about the wiki/faq/docs then it is on a wiki you are more than welcome to update and you can provide patches for docs in jira

I guess this issue still persists with the latest release of hibernate annotations 3.4.0.GA. If generating indexes is going to be part of foreign key generation, then we cannot have a single JPA entity model to seamlessly generate a DDL for different databases.

It would be nice to provide a different name for the index generated below in the Foreign Key annotation.
alter table user_im
add index fk_user_im_user_id (user_id),
add constraint fk_user_im_user_id
foreign key (user_id)
references user (id);

I would rather like to see an idx_ prefix for the generated index

alter table user_im
add index idx_user_im_user_id (user_id),
add constraint fk_user_im_user_id
foreign key (user_id)
references user (id);

Any pointers on how to achieve this?

Tried tweaking Hibernate NamingStrategy ?

I briefly did look into the code responsible for schema generation and the naming strategy, but I guess the foreign key name is used to generate the mentioned index also for MySQL database. So I am not sure, if it would be possible to differentiate the foreign key name and the index name in the code. Appreciate any inputs on this topic.