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