@OnDelete Hibernate annotation does not generate ON DELETE CASCADE for MySql

Question

I have two entities parent and child with a unidirectional relationship

class Parent {
    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    private Long id;
}

and

class Child {
    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    private Long id;

    @ManyToOne(optional = false)
    @JoinColumn(name = "parent_id")
    @OnDelete(action = OnDeleteAction.CASCADE)
    private Parent parent;
}

In the application.properties file I have configured

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.database-platform = org.hibernate.dialect.MySQL5InnoDBDialect

But when I run the application the following statement is created

...
alter table child add constraint FKi62eg01ijyk2kya7eil2gafmx foreign key (parent_id) references parent (id)
...

So there is no ON CASCADE DELETE as there should be. The tables are created each time I run the application and I checked if the method

org.hibernate.dialect.MySQL5InnoDBDialect#supportsCascadeDelete()

is really called (it is). I am using spring-boot-parent version 1.4.3, which uses Hibernate 5.11. Any ideas? I do not want to use a bi-directional relationship by the way.

Edit Thanks to @AlanHay I discovered that I omitted an important part. There actually is a third class involved

class Kindergarten {

    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    private Long id;

    @OneToMany(mappedBy = "kindergarten", fetch = FetchType.EAGER)
    @MapKeyJoinColumn(name = "parent_id") // parent_id causes the problem!
    private Map<Parent, Child> children;
}

and Child with Kindergarten looks actually like this

class Child {

    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    private Long id;

    @ManyToOne(optional = false)
    @JoinColumn(name = "parent_id")
    @OnDelete(action = OnDeleteAction.CASCADE)
    private Parent parent;

    @ManyToOne
    @JoinColumn(name = "kindergarten_id")
    private Kindergarten kindergarten;
}

and that is why the problem occurs. If you change "parent_id" in the MapKeyJoinColumn annotation to something not existing in Child as a column e.g. "map_id" the ON DELETE CASCADE is added to the foreign key parent_id in Child. If the parameter is the Child's column "parent_id" the ON DELETE CASCADE part will not be appended. Unfortunately the reason for this is not yet clear to me. Changing the parameter is no option because I want to use the existing link to parent of the child object.

Can't you use the JPA Cascade.DELETE http://stackoverflow.com/questions/19626535/how-to-cascade-delete-entities-with-unidirectional-manytoone-relationship-with — Essex Boy, Jan 04 '17 at 14:22
I take it that you mean CascadeType.REMOVE. This will not work if you delete the parent. What I want is that when you delete the parent, all childs are deleted as well. — jansolo, Jan 04 '17 at 14:35
In the Parent association where you define a `OneToMany` annotation add `orphanRemoval = true`. This should ensure deletes done by `EntityManager.remove()` or JPQL queries also delete child entities. It will not however delete child-entities if you delete the parent using native queries. — coladict, Jan 04 '17 at 14:47
Yes, that is what I meant. Sorry I misunderstood your question. Maybe the dialect class is faulty. I take it your read this http://stackoverflow.com/questions/7197181/jpa-unidirectional-many-to-one-and-cascading-delete — Essex Boy, Jan 04 '17 at 14:48
Just checked the sources. MySQLDialect overrides supportsCascadeDelete to return false, which tells the generator not to add that to the constraint. You could make a pull-request to the Hibernate team, but there might be a reason they've added it. Some times syntax is listed as supported, then you get an "Not yet implemented" error. It happened when I was trying to use arrays in an Oracle 11g db. Who knows starting which version it actually works. — coladict, Jan 04 '17 at 15:03
@coladict That is the reason I used org.hibernate.dialect.MySQL5InnoDBDialect which should fix the problem (it returns true and I like I said I have tested it) but in my setup it seems that the return value is ignored. — jansolo, Jan 04 '17 at 15:38
In additional to checking the dialect property, there is an additional check in `org.hibernate.tool.schema.internal.StandardForeignKeyExporter.getSqlCreateStrings(ForeignKey, Metadata)` which only appends 'on delete cascade' if a 2nd condition is true. You can set a breakpoint here and trace back to see why it is not appended. looking at the source it should be true however if the `@OnDelete(action = OnDeleteAction.CASCADE)` is set as you have it. See `org.hibernate.cfg.AnnotationBinder.processElementAnnotations(...)` — Alan Hay, Jan 05 '17 at 18:35
I have tested this against MySQL5 and it works fine if, and only if, it is a unidirectional @ManyToOne relationship whcih you say you have. Is the MySQL5InnoDBDialect definately being picked up? The properties you have set are Spring Boot specific. Is this a Spring Boot project as your question is not tagged with Spring boot? — Alan Hay, Jan 05 '17 at 19:20
That was a great hint @AlanHay. Thank you. The problem actually starts to occur if the third class Kindergarten is involved. I have edited the question accordingly. Sorry for the inconvenience. I thought it was irrelevant and wanted to be concise. Unfortunately this pinpoints the problem without providing a solution yet. Any idea? — jansolo, Jan 06 '17 at 14:33
One simple solution is to stop trying to use the non-JPA, Hibernate specific @OneDeleteCascade and use standard JPA Cascading attributes. Unless you are dealing with hundreds of records having JPA handle cascading deletes is likely to be perfectly ok rather than delegating to the database. In one of your comments above you appear to suggest that will not work. However you are incorrect in that assertion. — Alan Hay, Jan 06 '17 at 16:42
I did not say that it will not work. I said I do not want to implement it that way. The reason is that the top down relationship will result in hundreds of records in the top object just to make JPA work. But from the domain point of view this makes absolutely no sense (The names Parent and Child are just used for easier understanding). Please trust me on that. @OnDelete(action = OnDeleteAction.CASCADE) is the perfect solution. It just does not work with this Map setup. For whatever reason. My current solution will be to not use a Map but just a Set. Not perfect but works. — jansolo, Jan 10 '17 at 12:35

score 4 · Answer 1 · answered Jul 08 '17 at 16:02

4

Maybe a little late, but since it is one of the top posts when searching for 'hibernate ondelete generate cascade':

For some reason putting @OnDelete on the ManyToOne side in Mysql did not work for me, but it worked on the OneToMany side. So if you are unlucky, try it on the other side.

answered Jul 08 '17 at 16:02

Markus Barthlen

389
4
15

2

Wow, magic. Thanks. Either it is a bug, or the documentation is wrong: https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html#pc-cascade-on-delete – Scadge Aug 28 '18 at 12:09

score 0 · Answer 2 · answered Sep 19 '20 at 13:33

In my case I also had to place @OnDelete on the OneToMany side, next to that I also had to change the JpaVendorAdapter bean method. The adapter it returns must be set to org.hibernate.dialect.MySQL5InnoDBDialect like so:

adapter.setDatabasePlatform("org.hibernate.dialect.MySQL5InnoDBDialect");

@OnDelete Hibernate annotation does not generate ON DELETE CASCADE for MySql

2 Answers2

Linked