3

This question is related to this in relation to the fact that I have a one-way @ManyToOne relationship between child and parent. There are hundreds of thousands of child entries, and I do not want the parent to have a @OneToMany relationship with the children.

All works fine except for the fact that deleting the parent is not possible unless I delete the children first, and I am looking to achieve what Postgres easily provides through the ON DELETE CASCADE. I am using Postgres 9. I am also using JPA 2.0/Hibernate 4.1.7.

I am currently using the auto DDL creation feature of Hibernate, because I am frequently moving things around, so I don't want to manually add the ON DELETE CASCADE on the tables, because they will be deleted the next time round I do some changes.

Is there any way I can put the ON DELETE CASCADE constraint/trigger as part of the JPA @ManyToOne annotations in the child Entity? This way the database is created correctly automatically.

Community
  • 1
  • 1
jbx
  • 21,365
  • 18
  • 90
  • 144
  • JPA standard `@OneToMany(..., orphanRemoval=true)` is the normal solution (http://stackoverflow.com/q/306144/398670). You can't / don't want to use that, so you're looking for alternatives. right? – Craig Ringer Nov 14 '12 at 01:15
  • The OP is asking about `@ManyToOne` and specifically avoiding adding the inverse `@OneToMany`... – Steve Ebersole Nov 14 '12 at 12:42
  • @CraigRinger No I don't want to use `@OneToMany`. In my case I often have millions of rows associated associated with the parent. I never need the case of getting all the children for a parent top-down. I don't wish to add `@OneToMany` just for this scenario. – jbx Nov 14 '12 at 13:15
  • That's what I thought; I wanted to confirm that, and make it clear "for the record" what the standard approach is for others who find this question. – Craig Ringer Nov 14 '12 at 13:17

1 Answers1

5

Using JPA annotations, no. But Hibernate has @org.hibernate.annotations.OnDelete. 

    @ManyToOne
    @JoinColumn
    @OnDelete( action = OnDeleteAction.CASCADE )
    private Parent parent;

The EG is discussing adding something similar this as part of JPA 2.1

Steve Ebersole
  • 9,339
  • 2
  • 48
  • 46
  • Do you know if adding the ON DELETE CASCADE to the columnDefinition field of the @JoinColumn would also do the trick? – jbx Nov 14 '12 at 00:03
  • Actually I looked some more and `@OnDelete` is only currently supported for the collection side, not the (even uni-directional) many-to-one side. Sorry about that. And no, using columnDefinition won't work since columnDefinition targets the column definition, while `ON DELETE` behavior is part of the foreign-key. At the moment you'd have to manually switch this to work. I'll update my answer with an appropriate code sample... – Steve Ebersole Nov 14 '12 at 12:54
  • Hi, well I tried `@OnDelete` this morning as you suggested and actually with hbm2ddl.auto set to create (it doesn't seem to work with update) it works fine. It created an ON DELETE CASCADE trigger on the foreign key constraint of the child, exactly as I wish. So seems to work for me on `@ManyToOne` – jbx Nov 14 '12 at 13:11
  • Yeah, you are right. I was just going off the description. I tried myself and it worked for me too. – Steve Ebersole Nov 14 '12 at 13:32
  • One problem I have however, is that it is very very slow. I suspect its because the FK itself does not have an index on it. Do you know if there's another Hibernate annotation to also force the @ManyToOne to have an index? JPA does not seem to have one either :( – jbx Nov 14 '12 at 13:57
  • Thanks, was going to reply in fact that I found it in the meantime :). For anyone looking for it in the future: `@org.hibernate.annotations.Table(appliesTo = "childtable", indexes = { @Index(name = "child_parent_id_idx", columnNames = "parent_id") })` – jbx Nov 14 '12 at 14:35
  • With the index cascade deleting thousands of records becomes lightning fast btw :) – jbx Nov 14 '12 at 14:36