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:

MyAssembly.MySQL5InnoDBDialect, MyAssembly

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

using NHibernate.Dialect;

namespace MyAssembly
{
  /// This class is provided to NHibernate to force it to create tables
  /// for the InnoDB database engine. This is the only MySQL engine that allows
  /// foreign keys. For other engines, any foreign keys that NHibernate creates
  /// are silently ignored.
  public class MySQL5InnoDBDialect : MySQL5Dialect
  {
    public MySQL5InnoDBDialect()
    {
    }

    /// This string is added at the end of each CREATE TABLE statement.
    public override string TableTypeString
    {
      get { return " ENGINE=InnoDB"; }
    }

    public override bool HasSelfReferentialForeignKeyBug
    {
      get { return true; }
    }

    public override bool SupportsCascadeDelete
    {
      get { return true; }
    }
  }
}

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.