ManyToMay-Relations and Foreign Keys with on delete cascade

mrVisual · **Joined:** Mon Mar 01, 2004 9:59 am **Posts:** 8

Hi,

I have a ManyToMany-Relation between a Class Pizza and a Class Topping.

To map this, I use Hibernate Annotations:

@ManyToMany(fetch = FetchType.LAZY,cascade={CascadeType.ALL})
@JoinTable(
name = "pizza_topping",
joinColumns = @JoinColumn(name = "pizza_id"),
inverseJoinColumns = @JoinColumn(name = "topping_id"))
public List<Topping> getToppings() {
return toppings;
}

The Problem is, that this doesn't activates "on delete cascade" on the foreign keys between the jointable and the tables for Pizza and Order.

What to do, to achieve this?

Regards,
Manfred

thatmikewilliams · **Posted:** Sun Aug 05, 2007 11:28 am

Quote:

on the foreign keys between the jointable and the tables for Pizza and Order

Do you mean Pizza and Topping? Are you saying the entries in pizza_topping table aren't being deleted?

I created a simple test case with your annotations and everything seems to delete fine. Is this a bi-directional relationship i.e. does Topping have a list of Pizzas?

From my test case:

Code:

Hibernate: insert into Pizza (id) values (null)
Hibernate: call identity()
Hibernate: insert into Topping (id, name) values (null, ?)
Hibernate: call identity()
Hibernate: insert into Topping (id, name) values (null, ?)
Hibernate: call identity()
Hibernate: insert into pizza_topping (pizza_id, topping_id) values (?, ?)
Hibernate: insert into pizza_topping (pizza_id, topping_id) values (?, ?)

Hibernate: select pizza0_.id as id0_0_ from Pizza pizza0_ where pizza0_.id=?
Hibernate: select toppings0_.pizza_id as pizza1_1_, toppings0_.topping_id as topping2_1_, topping1_.id as id1_0_, topping1_.name as name1_0_ from pizza_topping toppings0_ left outer join Topping topping1_ on toppings0_.topping_id=topping1_.id where toppings0_.pizza_id=?

Hibernate: delete from pizza_topping where pizza_id=?
Hibernate: delete from Topping where id=?
Hibernate: delete from Topping where id=?
Hibernate: delete from Pizza where id=?

mrVisual · **Joined:** Mon Mar 01, 2004 9:59 am **Posts:** 8

Hi,

how did you delete the entries? - using session.delete(pizza) or using HQL ?. If I use the first approach it works; the second one doesn't work.

In this case, HQL seems to get directly translated to SQL and as Hibernate has not created a foreignkey with "on delete cascade" I get an error when the pizza has some Toppings associated.

Regards,
Manfred

thatmikewilliams · **Posted:** Sun Aug 05, 2007 2:07 pm

Hi Manfred
Yes, I used session.delete(pizza). Can I ask why you need to use HQL for deleting the object? I don't think hibernate supports delete on cascade type triggers. You'll probably have to issue the mapping table and topping deletes manually if you wish to use HQL.

Mike

mrVisual · **Joined:** Mon Mar 01, 2004 9:59 am **Posts:** 8

Hi,

because I have to delete a larger set of objects (delete from pizza where xyz > 30, for instance).

There must exist a way to achieve that. Hibernate creates a foreign key for this relation, using a statement like: alter table pizza_topping add constraint fk_xyz foreign key (pizza_id) references pizza(id).

What I need is a way to tell Hibernate that the following statement should be used instead: alter table pizza_topping add constraint fk_xyz foreign key (pizza_id) references pizza(id) on delete cascade.

I figured out how to do this with 1:n-Relations. In this case I would use the @Cascade Anotation. But @Cascade is not allowed for Many-To-Many-Relations ...

Regards,
Manfred

mrVisual · **Joined:** Mon Mar 01, 2004 9:59 am **Posts:** 8

Does any one has an idea, how to define a "on delete cascade"-foreign key using Hibernate Anotations?

Regards,
Manfred

christian · **Posted:** Thu Aug 09, 2007 12:43 pm

http://www.google.com/search?q=hibernat ... e%20delete

mrVisual · **Joined:** Mon Mar 01, 2004 9:59 am **Posts:** 8

I know those postings ... and they tell to use
@org.hibernate.annotations.OnDelete(...)

This solution works for me in the case of @OneToMany but NOT in the case of @ManyToMany

So, what to do, to get a "on delete cascade"-foreign key for a ManyToMany-Relation?

Regards,
Manfred

mrVisual · **Joined:** Mon Mar 01, 2004 9:59 am **Posts:** 8

Does anyone have an idea?
I can not imagine that I am the only person facing this issue ...

Thx and Regards,
Manfred

mark_in_gr · **Joined:** Mon Sep 05, 2005 4:48 pm **Posts:** 31

You aren't the only person facing this problem!!! I have been searching for a way to add ON DELETE CASCADE DDL to HBM generated DDL for days, with no luck.

This should be a very straightforward process, seeing it is a basic requirement of any RDBMS. Below I have a mapping of a class which has a set of objects which refer back to the same class, a sub-group in this case. Notice the on-delete setting childGroups key.

According to the hibernate docs, this should generate ON CASCADE DELETE DDL, or at least that is how I am reading it. By adding cascade="delete", a distinct delete sql command is being generated, which I find unnecessary. One would think that a single delete for the parent would suffice if the proper ON DELETE CASCADE were being generated by hbmddl.

What is actually happening is the delete sql commands are working fine until the last one executes, which is the one which deletes the parent row. Seeing this is being run in a single transaction, I suspect that somehow the Session thinks these objects are still persistent when in fact they have already been deleted.

Code:

<hibernate-mapping>
    
    <class name="com.xrite.ind.core.Group" table="tblGroup" >
        <id name="Id" type="long">
            <column name="groupID" />
            <generator class="native" />
        </id>
        
        <property name="name" column="name" lazy="false" length="50" type="string" not-null="true" unique="false" index="IDX_GROUP_NAME" />
        
        <set name="standards" lazy="true" cascade="save-update" inverse="true" order-by="name" >
            <key column="groupID" on-delete="cascade"/>
            <one-to-many class="com.xrite.ind.core.Color" />
        </set>
        
        <many-to-one name="parent" class="com.xrite.ind.core.Group" lazy="false" index="group_parent_idx" >
            <column name="group_parent_id" />
        </many-to-one>
        
        <!-- cascade="delete-orphan" -->
        <set name="childGroups" lazy="true" inverse="true" order-by="name" cascade="delete" >
            <key column="group_parent_id" on-delete="cascade"/>
            <one-to-many class="com.xrite.ind.core.Group"/>
        </set>   
        
    </class>
    
</hibernate-mapping>