many-to-many relationship problem

woodj · **Joined:** Tue Apr 24, 2007 6:39 pm **Posts:** 7

I'm having a problem with link tables and hibernate, or in other words, a many-to-many relationship problem. It is not hard to set up in hibernate; inserts and updates work fine, but the deletes fail if any of the parents share some children.

I've written an entry into my blog with more detail. It's all standard stuff, right out of the hibernate documentation. There seems to be either a gap in my understanding of how this relationship should work, or there should be another way to do it in hibernate.

I've scoured the forums looking for an answer, but have come up with nothing.

http://jwoodchip.blogspot.com/2007/05/h ... ables.html

The reason deleting an AccessLevel fails is because of the foreign keys on the link table. It always tries to delete the relationship as well as the Privilege, because of said FK's. But it lets you create these relationships (ie share Privileges among AccessLevels without problem). I've tried to prevent the delete from cascading, without success. I've tried telling the link table to take noaction when a key is deleted, without success. What I think should happen is that when you delete an AccessLevel, it should also delete the relationships for that AccessLevel, and then stop. Data integrity is always preserved this way.

So my question is, assuming this relationship is mapped properly, how do you delete an AccessLevel (without getting an error)?

Hibernate version:
3.2.3ga

Name and version of the database you are using:
Mysql 5.0.27-max

Mapping documents:

Code:

<class name="AccessLevel" table="AccessLevel">
    <id name="id" column="id">
        <generator class="native"/>
    </id>
    <property name="name"/>
    <set name="privileges" table="AccessLevel_Privilege">
        <key column="accessLevelId"/>
        <many-to-many class="Privilege" column="privilegeId"/>
    </set>
</class>

<class name="Privilege" table="Privilege">
    <id name="id" column="id">
        <generator class="native"/>
    </id>
    <property name="privilegeType"/>
    <property name="permissions"/>
</class>

Generated SQL

Code:

create table AccessLevel (
id bigint not null auto_increment, 
name varchar(255) not null, 
primary key (id)) type=InnoDB;

create table AccessLevel_Privilege (
accessLevelId bigint not null, 
privilegeId bigint not null, 
primary key (accessLevelId, privilegeId)) type=InnoDB;

create table Privilege (
id bigint not null auto_increment, 
privilegeType varchar(255) not null, 
permissions smallint not null, 
primary key (id), 
unique (privilegeType, permissions)) type=InnoDB;

alter table AccessLevel_Privilege add index FKC1FC75729CEB5337 (accessLevelId), add constraint FKC1FC75729CEB5337 foreign key (accessLevelId) references AccessLevel (id);
alter table AccessLevel_Privilege add index FKC1FC7572EDE1B639 (privilegeId), add constraint FKC1FC7572EDE1B639 foreign key (privilegeId) references Privilege (id);

Pseudocode to reproduce
create an AccessLevel with 3 privileges and persist
create another AccessLevel with same 3 privileges and persist
delete one of the AccessLevels

Error
Foreign key constraint exception when trying to delete the AccessLevel

vforvikash · **Posted:** Thu May 03, 2007 11:34 pm

just check with the property cascade="save-update" for the set tag.
or
on-delete="noaction" with the key tag

woodj · **Joined:** Tue Apr 24, 2007 6:39 pm **Posts:** 7

That's what I thought too, but neither of those worked (see my blog post). They in fact have no effect at all, from what I can tell - hibernate continues to issue deletes on the privilege table regardless. Actually I used cascade="none", so I'll give "save-update" a try too.

Thanks for the reply.

vforvikash · **Posted:** Fri May 04, 2007 12:27 am

did you tested with ondelete="noaction" for the key tag alone.
and even this happens with all update coz first it removes all rows then again add them to the set. so cascade="none" is the only available not cascade on "save only"

woodj · **Joined:** Tue Apr 24, 2007 6:39 pm **Posts:** 7

Okay I've tried all those options, and still no go. Just for completeness, here are the variations I've tried:

Code:

<set name="privileges" table="AccessLevel_Privilege" cascade="save-update">
    <key column="accessLevelId" on-delete="noaction"/>
    <many-to-many class="Privilege" column="privilegeId"/>
</set>

<set name="privileges" table="AccessLevel_Privilege">
    <key column="accessLevelId" on-delete="noaction"/>
    <many-to-many class="Privilege" column="privilegeId"/>
</set>

<set name="privileges" table="AccessLevel_Privilege" cascade="save-update">
    <key column="accessLevelId"/>
    <many-to-many class="Privilege" column="privilegeId"/>
</set>

<set name="privileges" table="AccessLevel_Privilege" cascade="none">
    <key column="accessLevelId"/>
    <many-to-many class="Privilege" column="privilegeId"/>
</set>

In each of those 4 cases, hibernate still insists on trying to delete the AccessLevel first, which of course immediately causes mysql to throw the foreign key constraint failure exception. It needs to delete the rows from the AccessLevel_Privilege first, and then additionally, not delete any Privileges.

Thanks

woodj · **Joined:** Tue Apr 24, 2007 6:39 pm **Posts:** 7

Well, I don't know how I missed this, but my faith is restored in hibernate. It turns out my original mapping was fine, but there was a bug in the delete implementation. With that fixed, Hibernate just deletes the AccessLevel_Privileges and the AccessLevel without hassle. Sorry for the bother!

regisdev · **Joined:** Wed Jan 03, 2007 5:03 pm **Posts:** 4

How did you solve this problem ? I have the same issue i m using hibernate 3.2.5 ga. And i try to delete in my many-to-many (Roles) just the relationship.

Code:

<class name="User" table="USER_USER" lazy="true">
....
<set name="roles" table="USER_USER_ROLE" lazy="false" cascade="none">
<key column="USER_ID"/>
<many-to-many column="ROLE_ID" class="Role" />
</set>
...
</class>

Code:

<class name="Role" table="USER_ROLE" lazy="true">
     
     <id name="id" column="ID" >
        <generator class="native">
           <param name="sequence">USER_ROLE_SEQ</param>
         </generator>
     </id>
     
     <property name="name" column="NAME" 
     type="string" not-null="true"/>
     
     <property name="description" 
     column="DESCRIPTION" 
     type="string"  />
  </class>

But every time i run my java class , hibernate try to delete the parent before the child and it throws ORA-02292: integrity constraint (RAW.FK7A99D0F69B44F47) violated - child record found. And it is normal when you check the log

Quote:

Hibernate: delete from USER_USER where ID=?

I discover i can fix with:

Code:

user.setRoles(null);
session.delete(user); //delete the user done !

This is the only way ???? Or maybe i miss something

==================================
Sorry for the mistake, it was my own java code which have a problems not hibernate sorry.....

I didn t refresh the user object when i assign new role to the user, so hibernate was confused....