NHibernate is an Object Relational Mapping library that can create a MySQL database for you from your code, using mapping information that you provide. As it does so, it will create foreign keys as necessary to reflect that data integrity that your code requires. However, MySQL only accepts foreign keys for its InnoDB table format. This means that any foreign keys that NHiberate creates are silently ignored. Your MySQL EER diagrams will not show any associations, since there aren’t any.

On some systems, the standard table format the MySQL uses is not InnoDB but rather MyISAM (or something else still). You can change in the configuration, but you may not have administrative access to the MySQL server. This means that whenever you create a table, you must specify the table engine to be used, like so:

CREATE TABLE User (
  Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Name VARCHAR(255),
  PRIMARY KEY (Id)
) ENGINE=InnoDB;

However, NHibernate doesn’t know about these things. When you tell it to create a database schema from code using the SchemaExport class, like this:

new SchemaExport(configuration).Execute(true, true, false);

…it will create tables without any ENGINE information. Still, there is a solution. In order to make NHibernate InnoDB-aware, add a new MySQL dialect:

public class MySQLInnoDBDialect : MySQL5Dialect
{
  public MySQLInnoDBDialect() { }

  public override string TableTypeString
  {
    get { return " ENGINE=InnoDB"; }
  }

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

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

You can now tell NHibernate to use this new dialect in your configuration file:

MyAssembly.MySQLInnoDBDialect, MyAssembly

Two things are important to remember here:

• Since the new dialect does not live in the NHibernate assembly, you must specify the name of the assembly it must be loaded from (MyAssembly, in the example);
• The dialect class should be public.

Now, NHibernate creates tables with the ENGINE=InnoDB tag. Also, in MySQL Workbench, your EER diagrams suddenly show associations when you reverse engineer your database: