ON DELETE CASCADE option not in generated when using ddl schema generation on Mysql

With the OnDelete annotation the DDL should be right. Could you check how are you configuring the SessionFactory, in specific which value are you using for the hbm2ddl.auto parameter.

UPDATE

• Regarding your issue with the UserProvider class. First the mapping seems to be bidirectional, but one side must be the owner side and the other must be the inverse side. Meaning the one that owns the relation is the one that persists the relation into the join table, and the other must be mapped with the mappedBy parameter and do not controls the relation. So the OneToMany with the mappedBy pointing to the user member of the UserProperty will be the inverse side, And UserProperty will be the owner side, and there should be the OnDelete annotation. But let me test it tomorrow to be sure, I am not in front of my dev station.

After investigating the issue I came up with the following methods to deal with DB schema generation (assuming you are using Hibernate as your JPA provider):

• Using the ddl schema generation, you can generate your DB schema. Using this option the schema will be created/updated while you start your web-server. If you use this method, in order to make sure your onDelete option is set to cascade you can use the OnDelete annotation. This worked for me just fine for a OneToOne relation (thanks to Angel Villalain) but for some reason it didn't work for a OneToMany relation. To workaround this gap I used Spring's ResourceDatabasePopulator:

The db-additions.sql file contains the queries that adapt my DB, in my case create the Ondelete Cascade. For instance:

ALTER TABLE `buysmartdb`.`users_providers` DROP FOREIGN KEY `FKB4152EEBBF9006F5` ;
ALTER TABLE `buysmartdb`.`users_providers` 
  ADD CONSTRAINT `FKB4152EEBBF9006F5`
  FOREIGN KEY (`User_Id` )
  REFERENCES `buysmartdb`.`users` (`User_Id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE;

Notice that the scripts triggered by the ResourceDatabasePopulator applies after the schema is generated by Hibernate ddl, which is good. I know that sinply due to the final result, I couldn't really make sure it is guaranteed.

• A second method is to generate the schema using maven, in compile time. There are a few ways of doing that, such as this one or that one.

I hope this will help someone..