I have a many-to-many relationship between classes User and Role. Class Role is mapped as inverse="true".
I don't want delete to cascade to the opposite entity, so if I delete a Role, I want to keep the associated Users.

My problem is: If I delete a Role, I get a

java.sql.SQLException: Integrity constraint violation

because the entry in the many-to-many table that maintains the relationship is not deleted. If I delete a User, everything works fine - my Role is not deleted, only the entry in the many-many table.

I could do something like

Set<User> users = role.getUsers()
for(User user : users) {
user.getRoles.remove(role);
}
session.delete(role);

but do I really have to? This seems so odd to me, because it just works on the User side where inverse="false".

Thanks in advance
Stefan




Hibernate version: 3.1 rc2

Mapping documents:

<hibernate-mapping>

<class
name="something.User"
table="BENUTZER"
>

<id
name="techid"
type="int"
column="TECHID">
<generator class="identity"/>
</id>


<!-- bi-directional many-to-many association to Role -->
<set
name="roles"
lazy="true"
cascade="none"
table="BENROLL"
>
<key>
<column name="BENUTZER"/>
</key>
<many-to-many
class="something.Role"
>
<column name="ROLLE"/>
</many-to-many>
</set>

</class>

<class
name="something.Role"
table="ROLLE"
>

<id
name="techid"
type="java.lang.Integer"
column="TECHID">
<generator class="identity"/>
</id>

<!-- bi-directional many-to-many association to User -->
<set
name="users"
lazy="true"
cascade="none"
table="BENROLL"
inverse="true"
>
<key>
<column name="ROLLE"/>
</key>
<many-to-many
class="something.User"
>
<column name="BENUTZER"/>
</many-to-many>
</set>
</class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():

lock(obj, LockMode.UPGRADE);
return super.delete(obj, prefix, messageVar);

Full stack trace of any exception that occurs:

ERROR [main, 15:57:59,890] (JDBCExceptionReporter.java:72) - Integrity constraint violation FK1D8B4B6896A3C480 table: BENROLL in statement [delete from ROLLE where TECHID=?]
ERROR [main, 15:57:59,890] (AbstractFlushingEventListener.java:299) - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: could not delete: [something.Role#2]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:69)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2298)
at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2430)
at org.hibernate.action.EntityDeleteAction.execute(EntityDeleteAction.java:65)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:243)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:227)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:145)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:296)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:877)
at org.springframework.orm.hibernate3.HibernateTemplate$27.doInHibernate(HibernateTemplate.java:788)

Name and version of the database you are using:

HSQL

The generated SQL (show_sql=true):

Hibernate: delete from ROLLE where TECHID=?

Debug level Hibernate log excerpt:

DEBUG [main, 16:29:14,062] (AbstractEntityPersister.java:2239) - Deleting entity: [something.Role#2]
DEBUG [main, 16:29:14,062] (AbstractBatcher.java:309) - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
Hibernate: delete from ROLLE where TECHID=?
DEBUG [main, 16:29:14,062] (AbstractBatcher.java:413) - preparing statement
DEBUG [main, 16:29:14,062] (AbstractBatcher.java:317) - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
DEBUG [main, 16:29:14,062] (AbstractBatcher.java:459) - closing statement
DEBUG [main, 16:29:14,078] (JDBCExceptionReporter.java:63) - could not delete: [something.Role#2] [delete from ROLLE where TECHID=?]
java.sql.SQLException: Integrity constraint violation FK1D8B4B6896A3C480 table: BENROLL in statement [delete from ROLLE where TECHID=?]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.executeUpdate(Unknown Source)

Try changing your something.User class's set to use cascade="delete-orphan" and you won't need to explicitly delete the set's objects.

Hi Lorban, thanks for replying.

It doesn't work for me:

If I put cascade="delete-orphan" on the User class, I get no effect.
If I put cascade="delete-orphan" on the Role class, I can delete the Role, but... as the documentation suggests, when I delete the last Role of a User, the User also gets deleted. That's not what I want - I want to keep the User, no matter if the deleted Role is the last one or not.

Sorry for my stupid answer I just read your question too fast and got confused.

you problem puzzeled me so I reproduced it and it looks like that's a limitation of Hibernate: only the inverse="false" side of the mapped relation is taking care of the in-between (aka relational) table rows.

This is consistent with what the documentation states here:

http://www.hibernate.org/hib_docs/v3/re ... irectional

so this does not qualify as a bug but I would say it's quite an annoying limitation nevertheless. Anyone else can comment on that one ?

It is probably not a good practice to model a pure many-to-many association. It is better to spilt it into two one-two-many associations and introduce business semantics for the many-to-many association. In your example a business class UserRoleAssociation or GrantedUserRoles can be introduced with additional attributes like creation- and last-modified user and timestamp information , start time and expiration date of the granted user rights etc. Then there is complete flexibilty in managing the GrantedUserRoles and the desired behaviour can be attained by modeling two parent / child relationships.

It looks like we are going to replace many-to-many by two sets anyway, so the problem should become irrelevant for us. But for the record, I agree that the problem seems to point to a hole in Hibernate.

Just curious, if you want the same behavior from a Role-to-User as you get from a User-to-Role (deleting one will delete the association but not the associated object) why is one mapped inverse="true" and the other not ?

One is mapped inverse="true" because the Hibernate reference documentation says I need to.

I copied the following from the HTML Hibernate reference:


1.3.6. Working bi-directional links

[...]

What about the inverse mapping attribute? For you, and for Java, a bi-directional link is simply a matter of setting the references on both sides correctly. Hibernate however doesn't have enough information to correctly arrange SQL INSERT and UPDATE statements (to avoid constraint violations), and needs some help to handle bi-directional associations properly. Making one side of the association inverse tells Hibernate to basically ignore it, to consider it a mirror of the other side. That's all that is necessary for Hibernate to work out all of the issues when transformation a directional navigation model to a SQL database schema. The rules you have to remember are straightforward: All bi-directional associations need one side as inverse. In a one-to-many association it has to be the many-side, in many-to-many association you can pick either side, there is no difference.


My experiments confirm this - with both sides mapped without inverse="false", inserts fail with a non unique primary key.

Best regards,
Stefan

Sorry, I meant to say this:

My experiments confirm this - with both sides mapped without inverse="true", inserts fail with a non unique primary key.