When you create a new table in MySQL without specifying the table engine to use, it’ll be MyISAM (MySQL configuration notwithstanding). This is all well and good, unless you want to use foreign keys and find it that these are only supported by the InnoDB table engine. No problem – you switch to InnoDB. But […]

This article was posted by Independent Software, a website and database application development company based in Maputo, Mozambique. Our website offers regular write-ups on technical and design issues, ranging from details at code level to 3D Studio Max rendering. Read more about Independent Software's philosophy, or get in touch with Independent Software.

When you create a new table in MySQL without specifying the table engine to use, it’ll be MyISAM (MySQL configuration notwithstanding). This is all well and good, unless you want to use foreign keys and find it that these are only supported by the InnoDB table engine. No problem – you switch to InnoDB. But what if it’s out of your hands?

When you use an Object Relational Mapping system like NHibernate, you can have it create your database tables for you. NHibernate will emit CREATE TABLE statements based on the table mappings you have integrated into your project. This is a great feature to include in your development cycle. On each build, your database is created fresh and you can have your code include test data to test your product with.

However, NHibernate’s CREATE TABLE statements do not specify which table engine to use, so it’s likely that MySQL will create MyISAM tables that do not support the foreign keys that NHibernate also creates, and you cannot reap the benefits of these keys. That means no cascades…

There’s a way to fix this, though. In your NHibernate configuration file, you can optionally specify the MySQL dialect that NHibernate must use. And this is precisely where the magic happens. Add the following line to your configuration file:

The MySQL dialect now refers to a class called MySQL5InnoDBDialect that you must create in your project:

The key is the TableTypeString property of the MySQL5Dialect class. You can override it to add a string of your choosing to each CREATE TABLE statement. Thus, you add “ENGINE=InnoDB” to each statement and presto: each table is now created as an InnoDB table and you will have foreign key support.

Did this article help you out? Please help us find more time to write useful guides & articles like this by donating a buck or two. It'll keep us coffee-fueled. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *