3

I have 2 tables.

// Accounts
@OneToMany(mappedBy="accounts", cascade=CascadeType.ALL)
@Cascade(org.hibernate.annotations.CascadeType.ALL)
private Set<Mails> mails;

// Mails
@ManyToOne(cascade=CascadeType.ALL)
@JoinColumn(name="user_id" , referencedColumnName="id", insertable=false, updatable=false)
private Accounts accounts;

How can I organize deleting all child rows when the parent row will be deleted? I have tried to set CascadeType.DELETE_ORPHAN for the Accounts table, but with that I can't delete parent rows if a child row exists.

blacktide
  • 10,654
  • 8
  • 33
  • 53
Eugene Mironenko
  • 35
  • 1
  • 1
  • 4
  • Can you show the `show create table` MySQL output for both tables? – marekful Jun 13 '13 at 00:30
  • @MarcellFülöp http://pastebin.com/N4y0LBmY – Eugene Mironenko Jun 13 '13 at 00:42
  • The SQL syntax is correct and is in accordance with my answer, however I don't see the `ON DELETE CASCADE` in the foreign key definition which is explicitly required by the InnoDB engine on the table level to allow for automatic child deletions. – marekful Jun 13 '13 at 00:53

3 Answers3

5

The problem probably is that the relation is defined in the wrong direction. Presuming that you have an account table with one-to-many relation to a mail table, you will end up not being able to delete a record from account until it has associated mail rows if you define the relation on account to reference mail. The correct way is to create the foreign key on mail to reference account.

With ON DELETE CASCADE, you tell MySQL that it should delete a row (whose table has the foreign key) if its parent (referenced by the key) is deleted. This operation is allowed by definition because in such a case the deleted record has references to it. In contrast, a deletion is not allowed if a record has references pointing to other records from it.

marekful
  • 14,986
  • 6
  • 37
  • 59
  • I forgot about ON DELETE CASCADE option. But now I can't to edit or remove parent rows. I'm tried to add foreign key manually and it works perfectly. ALTER TABLE Mails ADD FOREIGN KEY (user_id) REFERENCES Accounts(id) ON DELETE CASCADE ON UPDATE RESTRICT Where is error? – Eugene Mironenko Jun 13 '13 at 01:35
  • Error code 1451, SQL state 23000: Cannot delete or update a parent row: a foreign key constraint fails – Eugene Mironenko Jun 13 '13 at 01:36
  • With `ON UPDATE RESTRICT` MySQL disallows updating the _referenced_ field (Accounts.id) if it has references to it. Like `ON DELETE CASCADE` allows deletion of Account rows (and automatically deletes rows from Mails referencing the deleted Account row), `ON UPDATE CASCADE` allows updating the `id` filed in an Account row that is referenced by one or more Mail rows (and it will update the referencing field, (Mail.user_id) as well). – marekful Jun 13 '13 at 09:07
  • Yes. But How to set this parameters in hibernate? I added @OnDelete(action=OnDeleteAction.CASCADE) before OneToMany annotation, but it's not working. – Eugene Mironenko Jun 13 '13 at 09:15
  • Sorry, I don't know Hibernate, but I'd assume the same way you did for ON DELETE? `@OnUpdate(action=OnUpdateAction.CASCADE)`. This is just a guess, I really don't know Hibernate, was just trying to help you out the SQL level. – marekful Jun 13 '13 at 09:31
  • OnUpdate annotation does not exist :) Now I imagine what i should doing for right working. I try to search that in documentation. Thank you for help. – Eugene Mironenko Jun 13 '13 at 09:46
  • You're welcome. I suggest you ask a more specific question then like "How to apply 'ON UPDATE CASCADE' to a MySQL model in Hibernate". – marekful Jun 13 '13 at 09:56
  • I found this, look at the first and last answers: http://stackoverflow.com/questions/2592328/hibernate-cascading – marekful Jun 13 '13 at 10:08
3

You are using cascade=CascadeType.ALL in both entities. Try using that only at parent. This should work

//Accounts
@OneToMany(mappedBy="accounts", cascade=CascadeType.ALL,orphanRemoval=true)    
private Set<Mails> mails;

//Mails
 @ManyToOne
 @JoinColumn(name="user_id" , referencedColumnName="id" , nullable=false)
 private Accounts accounts;
srikanth yaradla
  • 1,225
  • 10
  • 13
  • orphanRemoval=true does not exist in hibernate. without it I have the same results – Eugene Mironenko Jun 13 '13 at 13:54
  • It does exist (may be you are using older version) and it was useful for me in cases like when you remove one of the child elements from the collection in parent that use Uni direction. And note that orphanRemoval has nothing to do with ON DELETE CASCADE http://stackoverflow.com/questions/4329577/jpa-2-0-orphanremoval-true-vs-on-delete-cascade – srikanth yaradla Jun 14 '13 at 15:10
  • i update hibernate and add orphanRemoval option, but it's not help:( – Eugene Mironenko Jun 18 '13 at 18:43
  • Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails – Eugene Mironenko Jun 18 '13 at 18:44
  • can you paste the code your mapping and the code for update/add/delete – srikanth yaradla Jun 20 '13 at 08:58
0

The "mappedBy" property indicates that the other side owns the relationship, so in your case Mails owns the relationship which is probably not what you want.

JPA Cascades only work in one direction, from the owner of the relationship.

So if you want Mails to be deleted when deleting an Account, you need to switch the owner of the relationship:

//Accounts
@OneToMany(cascade=CascadeType.ALL)
private Set<Mails> mails;

//Mails
@ManyToOne(mappedBy="mails")
@JoinColumn(name="user_id" , referencedColumnName="id", insertable=false, updatable=false)
private Accounts accounts;
samlewis
  • 3,950
  • 27
  • 27