Grails and MySQL - Enforcing Foreign Key Constraints

Problem

Imagine a scenario where you've two Grails domains called Book and BookAuthor (could by anything) with there respective MySQL tables. You've made sure that every time a Book is created, it must be associated to some BookAuthor by adding something like

BookAuthor author

in your Book domain. Also, you don't have to do anything in BookAuthor domain. Now when someone deletes a BookAuthor, Grails should enforce the foreign key constraint and does not allow it to happen. However, it does and the BookAuthor gets deleted. On top of that, the Book domain is now broken, because all the Books with the deleted BookAuthor as their author are now broken.

Solution

I was hinted that this can be prevented by using InnoDB as the storage engine for those particular tables. For details, read this, or this. For a quick solution, all you've to do is to add the following in your DataSource.groovy in the dataSource enclosure:

dialect = 'org.hibernate.dialect.MySQL5InnoDBDialect'

I dropped the database and let Grails recreate it because the tables have to be created with InnoDB as the storage engine. Note here that the default storage engine that MySQL uses while creating the tables is MyISAM.

This solved things for me. Now, whenever someone tries to delete a BookAuthor, which is associated to some Book, it is prevented by Grails with this message - "The BookAuthor id1 cannot be deleted" .. that's it :)

Happy coding, thanks!