Deleting assignments in Many-to-Many mappings

chambers · **Joined:** Thu Nov 24, 2005 6:23 pm **Posts:** 7

I have the following database structure:

Metadata 1--* Metadata_File_Asgt *--1 File

Nice and simple.

Now, when I want to delete a Metadata record, I want the delete to be cascade to the Metadata_File_Asgt, but NOT to the File (even if the file is orphaned, I still want to keep the record).

However, when I use the following mappings, I get a foreign key constraint violation, because Hibernate appears to be trying to delete the Files, even though those files are still associated with other Metadata records. I can understand that there may well be cases where you want both sides of the many-to-many relationship to be deleted, but I would have thought that that would be the exception, rather than the rule.

Metadata:

Code:

...
<set name="associatedFiles" table="metadata_file_asgt" cascade="save-update,delete">
            <key>
                <column name="metadata_id" />
            </key>
            <many-to-many column="file_id" class="au.net.isa.infobank.server.persistence.hibernate.FileHPO"  />
        </set>
...

File:

Code:

...
<set name="associatedMetadata" table="metadata_file_asgt" inverse="true" cascade="none">
            <key>
                <column name="file_id" />
            </key>
            <many-to-many column="metadata_id" class="au.net.isa.infobank.server.persistence.hibernate.MetadataHPO"  />
        </set>
...

I have seen many threads in these forums trying to get the same answers, but so far the only answers I have found are not solutions: they are merely workarounds, such as:
1) Cascade the deletes at the database level. Not a proper solution. You use an OR mapping technology such as Hibernate to make managing relationships etc simpler and (as much as possible) platform independent. Database level modifications don't help with either of these.

2) manually remove each associated by
i) Finding the record to be deleted.
ii) Find each individual associated record
iii) Remote each individual associated record from the parent record's collection
iv) Save the parent record
v) Delete the parent record.
Not a proper solution. Hibernate should be handling the relationship mappings, not me. It also seems to be extremely inefficient, particularly if you are dealing with large collections of associated records.

3) Create an object for each assignment table and map everything as one-to-many. This struck me as a rather odd solution: why would Hibernate support many-to-many mappings if you shouldn't use them? Many-to-Many mappings are a great idea and reduce the amount of code required to create/update records. Adds and Updates work extremely well. There just seems to be a bit of a problem with deletes.

4) Don't use "exotic mappings". This one made me laugh. What is "exotic" about a simple many-to-many mapping like this one? The reference manual says:

Quote:

Good usecases for a real many-to-many associations are rare. Most of the time you need additional information stored in the "link table".

All I can say to that is "rubbish!"

I am fairly new to Hibernate, so there may be a simple solution for this that I have just overlooked. However, given the responses to similar posts in these forums, I don't think this is the case.

It seems that the second 'solution' listed above is the best work-around, but I don't understand why you would need to go to such lengths just to delete a simple assignment. All you need to do is execute a "delete from metadata_file_asgt where metadata_id = X".

Any help in this matter would be greatly appreciated.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Turn of cascade deletions in your set. That cascade doesn't mean "from associateFiles to metadata_file_asgt", it means "from associatedFiles to File". If you change it to cascade="save-update", then deleting an item from the associatedFiles set will delete the row in metadata_file_asgt but not from File. I think that's what you want?

chambers · **Joined:** Thu Nov 24, 2005 6:23 pm **Posts:** 7

Thank you for your quick reply. It was indeed a simple solution :)

I have removed the delete from the cascade attribute for the associatedFiles set, and now deleting a Metadata record correctly deletes it's Metadata_File_Asgt records while leaving the File records themselves alone.

Does this mean that when using many-to-many relation mappings, deletes are implicitly cascaded to the assignment table? Ie, deleting the primary record automatically removes the associated records from its sets, which are then removed from the assignment tables when the delete is commit?

Does this also apply to other relationships? If I had a non-inverse one-to-many relationship mapping from Metadata to TableA, would the associated records in TableA be deleted automatically when the Metadata record is deleted, or do you need to to explicit set the cascade attibute to include deletes in this case?

Cheers.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

It applies in any case where there is an unmapped join table. For example, if you have a <join> attribute to create a single pojo out of two tables (linked one-to-one by primary key). If you had created your many-to-many as the more common many-to-one-to-many, then the cascade would apply only to the join table, and you'd have to cascade from the join table to the final table in the relationship. A pure many-to-many like you have looks after all of that for you.

hifan · **Joined:** Fri Jul 08, 2005 6:28 am **Posts:** 4

In a similar setting, we experienced this behaviour also. But this
"looking after all of that for me" for many-to-many seems only true
for the one side that is not inverse. So, in this example, if you
delete a file, its associations with metadata will not be deleted
and you have to do this by hand. Right?

This is not very nice to handle, if you have a lot of many-to-many
associations and always one arbitrary side is inverse (as there is
no good reason for a special side to be inverse or not).
In some cases the system does everything for you, in others
you have to do it yourself, and you need to know what case it is.

Or am I wrong here with something?

funny · **Joined:** Thu Jun 01, 2006 5:46 pm **Posts:** 1

tenwit wrote:

Turn of cascade deletions in your set. That cascade doesn't mean "from associateFiles to metadata_file_asgt", it means "from associatedFiles to File". If you change it to cascade="save-update", then deleting an item from the associatedFiles set will delete the row in metadata_file_asgt but not from File. I think that's what you want?

By looking at the forum topic, I was able to solve similar problem.

So, thanks to both tenwit and chambers.

steve · **Posted:** Thu Jun 01, 2006 10:30 pm

The thing to keep in mind is that both many-to-many and <join/> represent what Hibernate considers a "value type" semantic. Value-types are different from entity types in that cascades are not needed to have Hibernate write data to the database for these.

In the case of a many-to-many, it is the entries in the association table specifically which are considered value types. Any cascades you apply on a many-to-many mapping actually refer to the "target table" entity which is what you saw with cascade delete enabled.

Just thought the conceptualization behind it might be helpful here.

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

I have tried what has been suggested here, removing "delete" from the cascade setting of the non-inverse side, and leaving "save-update', however, I am still receiving a foreign key constraint violation on my association table foreign key.

My situation is, I have a many-to-many association between a set of Tolerances and a Set of Standards. A Standard object is subclassed from a parent Color object. I was curious as to whether this may be part of the problem.

An additional object, a Group, has a one-to-many association to Standards(which again, are sub-classed from Color).

What I want is the ability to delete an entire Group(thus, deleting all of it's Standards/Colors AND the associations between the now deleted Standards and it's Tolerances). I want to be able to do this by way of a single Java code statement:

Code:

session.delete(group);

I would assume that Hibernate would deal with this in one of two ways:

Allowing an ON DELETE CASCADE to be added to the foreign key of the association table.

Allowing separate delete statements to be generated based on a cascade="delete" setting on the mapping which would perform the delete statements in the appropriate order, in this case, deleting the association then deleting the Standard Row

Neither situation, however, appears to be possible from any mapping declaration I have seen, and I have poured over a large number of threads at the forums.

I am using MySQL 5.0 for the database. If I manually hack the DDL after hbm2ddl generates it, adding the ON DELETE CASCADE to the foreign key on the association table which is throwing the error, I am able to remove the Standard row when the association row is present. Leaving the ON DELETE CASCADE out of the DDL and attempting to delete the Standard row results in the exact same foreign key constraint exception I am seeing when I execute session.delete(group) through Hibernate.

So . . . is there a way, using a many-to-many, to remove one side of the association and the associated row itself? Is on-delete=cascade the only way to generate ON DELETE CASCADE for DDL through HBM2DDL? If so, this won't work for many-to-many associations, and if Hibernate will not generate a separate delete statement for the association table and execute that statement prior to deleting the Standard, I am screwed.

My mappings are as follows:

Group

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" cascade="save-update" >
            <column name="group_parent_id" />
        </many-to-one>
        
        <!-- cascade="delete-orphan" -->
        <set name="childGroups" lazy="true" inverse="true" order-by="name">
            <key column="group_parent_id" on-delete="cascade"/>
            <one-to-many class="com.xrite.ind.core.Group"/>
        </set>
        
        <set name="userGroups" lazy="true" table="tblUserGroup_DataGroup" inverse="true" >
            <key column="groupID"/>
            <many-to-many column="userGroupID" class="com.xrite.ind.backcheck.users.UserGroup"/>
        </set>
        
        <set name="tolerances" lazy="false" table="tblTolerances_DataGroup" >
            <key column="groupID"/>
            <many-to-many column="ToleranceID" class="com.xrite.ind.core.tolerances.Tolerance"/>
        </set>
        
    </class>
    
</hibernate-mapping>

Color, the parent of Standard:

Code:

<hibernate-mapping>
    
    <class name="com.xrite.ind.core.Color" table="tblColor" discriminator-value="Color">
        <id name="Id" type="long">
            <column name="colorID" />
            <generator class="native" />
        </id>
        
        <discriminator column="color_type" type="string" length="15" />
        
        <property name="name" column="name" type="string" not-null="true" index="IDX_COLOR_NAME" /> 
        <property name="description" column="description" type="string" /> 
        <property name="generatedFromLAB" column="generatedFromLAB" type="boolean"/>
        <property name="dateMeasured" column="dateMeasured" type="timestamp" not-null="true" />
        <property name="note" column="note" type="string" length="1000" />
        <property name="opacity" column="opacity" type="double" /> 
        <property name="haze" column="haze" type="double" />
        <property name="averagingMeasurementCount" column="averagingMeasurementCount" type="integer" /> 
        <property name="rgb" column="rgb" type="integer" not-null="true" />
        <property name="l" column="l" type="double" />
        
        <set name="reflectanceDataSet" lazy="false" inverse="true" sort="com.xrite.ind.core.ReflectanceDataSort" cascade="save-update"  >
            <key column="fkColor" on-delete="cascade" />
            <one-to-many class="com.xrite.ind.core.ReflectanceData" />
        </set>
        
        <set name="tags" table="tblColorTags"  lazy="false" >
            <key column="ColorID" />
            <many-to-many column="tagID" class="com.xrite.ind.core.Tag" />
        </set>
        
        <set name="customPropertyValues" cascade="all" lazy="true" >
            <key column="ColorID"/>
            <one-to-many class="com.xrite.ind.core.CustomPropertyValue"/>
        </set>
        
        <list name="measAttributeValues" cascade="all" lazy="false" >
            <key column="ColorID"/>
            <index column="attribute_index" />
            <one-to-many class="com.xrite.ind.core.MeasAttributeValue"/>
        </list>
        
        <subclass name="com.xrite.ind.core.Standard" discriminator-value="Standard">
            
            <property name="masterPanel" column="masterPanel" type="boolean" />
            <property name="panelAutoName" column="panelAutoName" type="boolean" />           
            
            <many-to-one name="group" class="com.xrite.ind.core.Group" lazy="false" index="IDX_STANDARD_NAME_GROUP" >
                <column name="groupID" />
            </many-to-one>
            
            <set name="samples" lazy="true" cascade="save-update" inverse="true" sort="com.xrite.ind.core.ColorDateSort"  >
                <key column="standardID" on-delete="cascade" />
                <one-to-many class="com.xrite.ind.core.Sample" />
            </set>
            
            <set name="tolerances" table="tblStandardTolerances" cascade="save-update" lazy="false" >
                <key column="standardID" />
                <many-to-many column="ToleranceID" class="com.xrite.ind.core.tolerances.Tolerance" />
            </set>
        </subclass>
        
        <subclass name="com.xrite.ind.core.Sample" discriminator-value="Sample">           
          
            
            <many-to-one name="standard" class="com.xrite.ind.core.Standard" not-null="true" fetch="join" >
                <column name="standardID" />
            </many-to-one>    
            
        </subclass>
        
    </class>
</hibernate-mapping>

Tolerances:

Code:

<hibernate-mapping>
    
    <class name="com.xrite.ind.core.tolerances.Tolerance" table="tblTolerance" discriminator-value="Tolerance">
        <id name="ID" type="long">
            <column name="ToleranceID" />
            <generator class="native" />
        </id>
        
        <discriminator column="tolerance_type" type="string" length="25" />
        
        <property name="name" column="name" type="string" />
        <property name="WarningLevel" column="WarningLevel" type="double" /> 
        <property name="globalTolerance" column="globalTolerance" type="boolean" /> 
        
        <component name="angle" class="com.xrite.ind.core.Angle">
            <property name="angleValue" type="java.lang.String" column="angleValue" />
        </component>
        
        <property name="illumObs" column="illumObs" type="com.xrite.ind.backcheck.dao.enums.HibIlluminantObserverState" />
        
        <set name="Standards" table="tblStandardTolerances" inverse="true" cascade="none" >
            <key column="ToleranceID" />
            <many-to-many column="StandardID" class="com.xrite.ind.core.Standard"/>
        </set>
</hibernate-mapping>

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Add not-null="true" to the <key> elements that are causing the constraint violation. This ensures that the elements in the various sets are deleted before the entities contains the sets.

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

tenwit wrote:

Add not-null="true" to the <key> elements that are causing the constraint violation. This ensures that the elements in the various sets are deleted before the entities contains the sets.

I tried this, using various permutations, and am still getting the same constraint violation exception.

Here is what I tried:

Iteration One:

Colors

Code:

<set name="tolerances" table="tblStandardTolerances" cascade="save-update" lazy="false" >
                <key column="standardID" not-null="true" />
                <many-to-many column="ToleranceID" class="com.xrite.ind.core.tolerances.Tolerance" />
            </set>

Tolerances

Code:

<set name="Standards" table="tblStandardTolerances" inverse="true" cascade="none">
            <key column="ToleranceID" not-null="true" />
            <many-to-many column="StandardID" class="com.xrite.ind.core.Standard"/>
        </set>

Iteration Two:

Colors

Code:

<set name="tolerances" table="tblStandardTolerances" cascade="save-update" lazy="false" >
                <key column="standardID"  />
                <many-to-many column="ToleranceID" class="com.xrite.ind.core.tolerances.Tolerance" />
            </set>

Tolerances

Code:

<set name="Standards" table="tblStandardTolerances" inverse="true" cascade="none">
            <key column="ToleranceID" not-null="true" />
            <many-to-many column="StandardID" class="com.xrite.ind.core.Standard"/>
        </set>

Iteration Three:

Colors

Code:

<set name="tolerances" table="tblStandardTolerances" cascade="save-update" lazy="false" >
                <key column="standardID" not-null="true" />
                <many-to-many column="ToleranceID" class="com.xrite.ind.core.tolerances.Tolerance" />
            </set>

Tolerances

Code:

<set name="Standards" table="tblStandardTolerances" inverse="true" cascade="none">
            <key column="ToleranceID" />
            <many-to-many column="StandardID" class="com.xrite.ind.core.Standard"/>
        </set>

Unless I misunderstood the use of the not-null="true" here, it appears that all variations have been tried. I am not sure why this would affect the ordering/cascading of associated rows, deleting them in this case, prior to the actual left or right side table's rows? I would think that the following would be more applicable in this case:

Code:

<set name="Standards" table="tblStandardTolerances" inverse="true" cascade="none">
            <key column="ToleranceID" on-delete="cascade"/>
            <many-to-many column="StandardID" class="com.xrite.ind.core.Standard"/>
        </set>

However, on-delete="cascade" is NOT allowed for many-to-many relationships. The reason for this is unclear and any Hibernate docs or forum postings I have read which have attempted to explain this design seem to focus on the point that cascading deletes for a many-to-many makes no sense, but I disagree. All I want to do here is remove the associated row first, then one side(rows from one side/table of the many-to-many).

Take for example, the following database:

Table TBLCOLOR
ColorID
--------
1

Table TBLTOLERANCES
ToleranceID
--------------
1
2
3
4
5

Table TBLSTANDARDTOLERANCES
StandardID ToleranceID
------------- -------------
1 1
1 2
1 3
1 4
1 5

When I delete ColorID=1 from TBLCOLOR, first all of the rows from TBLSTANDARDTOLERANCES where StandardID = 1 need to be removed!!! This would be the affect if ON DELETE CASCADE were able to be applied to the foreign key constraint on TBLSTANDARDTOLERANCES which is being violated here! Below is the code generated by way of HBM2DDL

Code:

CREATE TABLE `tblstandardtolerances` (
  `standardID` bigint(20) NOT NULL,
  `ToleranceID` bigint(20) NOT NULL,
  PRIMARY KEY  (`ToleranceID`,`standardID`),
  KEY `FK9E5C4D01904DBD92` (`ToleranceID`),
  KEY `FK9E5C4D016AFB6C9A` (`standardID`),
  CONSTRAINT `FK9E5C4D016AFB6C9A` FOREIGN KEY (`standardID`) REFERENCES `tblcolor` (`colorID`),
  CONSTRAINT `FK9E5C4D01904DBD92` FOREIGN KEY (`ToleranceID`) REFERENCES `tbltolerance` (`ToleranceID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This is the desired DDL:

Code:

CREATE TABLE `tblstandardtolerances` (
  `standardID` bigint(20) NOT NULL,
  `ToleranceID` bigint(20) NOT NULL,
  PRIMARY KEY  (`ToleranceID`,`standardID`),
  KEY `FK9E5C4D01904DBD92` (`ToleranceID`),
  KEY `FK9E5C4D016AFB6C9A` (`standardID`),
  CONSTRAINT `FK9E5C4D016AFB6C9A` FOREIGN KEY (`standardID`) REFERENCES `tblcolor` (`colorID`) ON DELETE CASCADE,
  CONSTRAINT `FK9E5C4D01904DBD92` FOREIGN KEY (`ToleranceID`) REFERENCES `tbltolerance` (`ToleranceID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

After manually dropping the FK9E5C4D016AFB6C9A and re-adding it w/ON DELETE CASCADE, then executing "delete from TBLCOLOR where colorID = 1", it works(as it should!!!).

So, with all this said, why is it so difficult to have Hibernate generate the DDL to do this when you can manually set up your schema w/ordinary DDL creation statements and this all works?

I have proven that the code which creates the CONSTRAINT `FK9E5C4D016AFB6C9A` FOREIGN KEY (`standardID`) REFERENCES `tblcolor` (`colorID`) is the following mapping in the Tolerances.hbm.xml file:

Code:

<set name="Standards" table="tblStandardTolerances" inverse="true" cascade="none">
            <key column="ToleranceID" />
            <many-to-many column="StandardID" class="com.xrite.ind.core.Standard"/>
        </set>

Is there ANY way in which I can get ON DELETE CASCADE to be added to the foreign key DDL? This would solve my problem.

Here is the exception from the stacktrace:

Code:

Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`xcolorqc/tblstandardtolerances`, CONSTRAINT `FK9E5C4D016AFB6C9A` FOREIGN KEY (`standardID`) REFERENCES `tblcolor` (`colorID`))

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

I have implemented a hack to get this working for now, using <database-object><create> to drop and re-add the constraints on the association tables, as seen below:

Code:

<database-object>
        <create>
            alter table tblstandardtolerances drop foreign key FK9E5C4D016AFB6C9A
        </create>
        <drop></drop>
     </database-object>
     <database-object>
        <create>
            alter table tblstandardtolerances add constraint FK9E5C4D016AFB6C9A foreign key(standardID) references tblcolor(colorID) ON DELETE CASCADE
        </create>
        <drop></drop>
    </database-object>

It would be nice if this behavior could be achieved through the many-to-many mapping declaration, such as what was previously mentioned, where on-delete="cascade" could be added. There already exists the ability to create a unique constraint for the foreign key constraint column by way of unique="true"(which in affect makes the relationship one-to-many, which is arguably of little use), so why not add on-delete="cascade"

This is a very big problem in my opinion and needs to be addressed!

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I hadn't realized that the problem was with the join table. Normally, there's no issue with that. There's no need for any cascade to be specified in this case: hibernate always deletes the join rows from tables specified in the table="" attribute of a collection mapping. The cascade="" part applies to the collection rows, not the join rows.

Going back to your original problem definition:

mark_in_gr wrote:

What I want is the ability to delete an entire Group(thus, deleting all of it's Standards/Colors AND the associations between the now deleted Standards and it's Tolerances). I want to be able to do this by way of a single Java code statement:
session.delete(group);

When you delete an instace of Group, you would want a cascade delete to its Standards collection, and a cascade delete to its Colors collection. You would not want a cascade from the deleted Standards to their Tolerances, as that would attempt to delete rows from the Tolerance table. When instances of Standards (either normally or via a cascade from Group), the join table between Standards and Tolerance will be cleaned up: you don't have to do anything for that to happen.

If there is a ternary relationship in there somewhere, then it is possible that you may have to specify the delete order somehow. I've never come across that problem though, so I've always assumed that hibernate figures that out for us. You might cast your eye over the Ternary Associations section of the ref docs.

Hope that helps.