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

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:

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:

… 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:

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

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:

MySQL Workbench EER diagram with associations

MySQL Workbench EER diagram with associations

Leave a Reply

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