Grails GORM and MYSQL cascade delete problem

Question

I have a table User that has many child tables defined in User class under the static hasMany grails.

I have no problem when doing User.get(3).delete() in grails. It automatically delete that user and all its child table rows. But when I want to perform the same operation in MySQL workbench. I get Error thrown by MySQL:

ERROR 1451: Cannot delete or update a parent row: a foreign key constraint fails (`test_db`.`search_stat`, CONSTRAINT `FK7A3CFFB6E64DB41` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`))
SQL Statement:
DELETE FROM `test_db`.`user` WHERE `id`='3'

I dont know what is the problem here with MySQL.

Check the definition of the table in MySQL. Do the foreign keys have "ON DELETE CASCADE" set, or is GORM/Hibernate doing the cascade in code behind the scenes? — Adam Vandenberg
I checked that in MYSQL workbench but under the Foreing Keys tab it does not show any foreign keys. Foreing Key Names and Reference Table tabs under that are blank. When I click on the blank, it automatically creates fk_user_1 and click the next tab, a drop down shows all the tables in the db for me to choose one as the referenced table. But I think something is wrong and I should not need to recreate the foreign keys. I tried to that delete commanind in the mysql command line version I get the same error thrown. — Alan McCloud
On the other hand the Grails GORM documentation clearly says the belongsTo automatically creates the cascade on delete/update behaviour. So this behaviour should have gone into db schema when GORM created tables in MySQL db. — Alan McCloud

Jörg Brenninkmeyer Jörg Brenninkmeyer · Accepted Answer · 2010-12-11T13:49:50

Where did you read about the "ON DELETE CASCADE" in the documentation? I haven't found it here. The poster of this post had to manually add this as well to get the desired behaviour.

If grails is really supposed to add this, have you tried deleting the DB and having it re-created by grails (at least in development environment)? Maybe the current schema was generated before you added the belongsTo or something?

Also check out this Blog post about GORM Gotchas regarding hasMany and belongsTo.

Grails GORM and MYSQL cascade delete problem

1 Answers