Hibernate version:
3.0.5

Name and version of the database you are using:
MySQL 4.1.7

I am having some trouble trying to create a mapping where a child table may have multiple parents and then having cascade delete the child when all the parents are gone.

Basically I have a parent table that looks like this:

PARENT
---------
id
detail1
detail2
child_id (not null) (FK to child.id)

and a child table like this:

CHILD
--------
id
detail3
detail4

I have a foreign key from PARENT.child_id to CHILD.id.

Note, two or more parents may have the same children, but a child may only have one parent. (I am talking abstractly, not about humans or other types of animals.) :)

In the mapping of the parent class I have a <many-to-one> relationship to the child. In the mapping of the child I have a <set> with a <one-to-many> relationship to the parent.

What I would like to happen:
When all the parents are deleted, the orphaned child goes away.

So, I have cascade="all-delete-orphan" in the <many-to-one> area of the parent class mapping.

What happens:
1) I create 2 parent objects with the same child object. I save the parents and both parents and the child are properly saved.
2) I start a transaction, then delete the first parent. Hibernate deletes the first parent and cascades the delete to the child, which in turn cascades the delete back up to the second parent. I then commit the transaction. At this point the child and both parents have been removed from the db.

I would like the child to stay around until the second parent is removed.

If I try to delete the second parent I get :
ERROR main org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.HibernateException: Unexpected row count: 0 expected: 1

Any suggestions?

Any help would be greatly appreciated.


Mapping documents:


The generated SQL (show_sql=true):
Hibernate: insert into child (detail3, detail4) values (?, ?)
Hibernate: insert into parent (detail1, detail2, child_id) values (?, ?, ?)
Hibernate: insert into parent (detail1, detail2, child_id) values (?, ?, ?)
Hibernate: delete from parent where id=?
Hibernate: delete from child where id=?

Debug level Hibernate log excerpt:

Start transaction then issue delete on 1st parent:
DEBUG main org.hibernate.event.def.DefaultDeleteEventListener - deleting a transient instance
DEBUG main org.hibernate.event.def.DefaultDeleteEventListener - deleting [Parent#72]
DEBUG main org.hibernate.impl.SessionImpl - setting cache mode to: GET
DEBUG main org.hibernate.engine.Cascades - processing cascade ACTION_DELETE for: Parent
DEBUG main org.hibernate.engine.Cascades - done processing cascade ACTION_DELETE for: Parent
DEBUG main org.hibernate.impl.SessionImpl - setting cache mode to: NORMAL
DEBUG main org.hibernate.engine.Cascades - id unsaved-value: null
DEBUG main org.hibernate.engine.Cascades - id unsaved-value: null
DEBUG main org.hibernate.engine.Cascades - id unsaved-value: null
DEBUG main org.hibernate.engine.Cascades - id unsaved-value: null
DEBUG main org.hibernate.engine.Cascades - id unsaved-value: null
DEBUG main org.hibernate.impl.SessionImpl - setting cache mode to: GET
DEBUG main org.hibernate.engine.Cascades - processing cascade ACTION_DELETE for: Parent
DEBUG main org.hibernate.engine.Cascades - cascading to delete: Child
DEBUG main org.hibernate.engine.Cascades - id unsaved-value: null
DEBUG main org.hibernate.event.def.DefaultDeleteEventListener - deleting a transient instance
DEBUG main org.hibernate.event.def.ReattachVisitor - collection dereferenced while transient [Child.parents#30]
DEBUG main org.hibernate.event.def.DefaultDeleteEventListener - deleting [Child#30]
DEBUG main org.hibernate.impl.SessionImpl - setting cache mode to: GET
DEBUG main org.hibernate.impl.SessionImpl - setting cache mode to: GET
DEBUG main org.hibernate.impl.SessionImpl - setting cache mode to: GET
DEBUG main org.hibernate.impl.SessionImpl - setting cache mode to: GET
DEBUG main org.hibernate.engine.Cascades - done processing cascade ACTION_DELETE for: Parent
DEBUG main org.hibernate.impl.SessionImpl - setting cache mode to: NORMAL

Commit transaction:
DEBUG main org.hibernate.transaction.JDBCTransaction - commit
DEBUG main org.hibernate.impl.SessionImpl - automatically flushing session
DEBUG main org.hibernate.event.def.AbstractFlushingEventListener - flushing session
DEBUG main org.hibernate.event.def.AbstractFlushingEventListener - processing flush-time cascades
DEBUG main org.hibernate.event.def.AbstractFlushingEventListener - dirty checking collections
DEBUG main org.hibernate.event.def.AbstractFlushingEventListener - Flushing entities and processing referenced collections
DEBUG main org.hibernate.event.def.AbstractFlushingEventListener - Processing unreferenced collections
DEBUG main org.hibernate.event.def.AbstractFlushingEventListener - Scheduling collection removes/(re)creates/updates
DEBUG main org.hibernate.event.def.AbstractFlushingEventListener - Flushed: 0 insertions, 0 updates, 2 deletions to 2 objects
DEBUG main org.hibernate.event.def.AbstractFlushingEventListener - Flushed: 0 (re)creations, 0 updates, 1 removals to 0 collections
DEBUG main org.hibernate.pretty.Printer - listing entities:
DEBUG main org.hibernate.pretty.Printer - Parent{detail1=null, detail2=null, child=Child#30, id=72}
DEBUG main org.hibernate.pretty.Printer - Child{detail3=null, detail4=null, id=30}
DEBUG main org.hibernate.event.def.AbstractFlushingEventListener - executing flush
DEBUG main org.hibernate.persister.entity.BasicEntityPersister - Deleting entity: [Parent#72]
DEBUG main org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
DEBUG main org.hibernate.SQL - delete from parent where id=?
Hibernate: delete from parent where id=?
DEBUG main org.hibernate.jdbc.AbstractBatcher - preparing statement
DEBUG main org.hibernate.type.IntegerType - binding '72' to parameter: 1
DEBUG main org.hibernate.persister.entity.BasicEntityPersister - Deleting entity: [Child#30]
DEBUG main org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
DEBUG main org.hibernate.jdbc.AbstractBatcher - closing statement
DEBUG main org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
DEBUG main org.hibernate.SQL - delete from child where id=?
Hibernate: delete from child where id=?
DEBUG main org.hibernate.jdbc.AbstractBatcher - preparing statement
DEBUG main org.hibernate.type.IntegerType - binding '30' to parameter: 1
DEBUG main org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
DEBUG main org.hibernate.jdbc.AbstractBatcher - closing statement
DEBUG main org.hibernate.event.def.AbstractFlushingEventListener - post flush
DEBUG main org.hibernate.jdbc.JDBCContext - before transaction completion
DEBUG main org.hibernate.impl.SessionImpl - before transaction completion
DEBUG main org.hibernate.transaction.JDBCTransaction - committed JDBC Connection
DEBUG main org.hibernate.jdbc.JDBCContext - after transaction completion
DEBUG main org.hibernate.impl.SessionImpl - after transaction completion
DEBUG main org.hibernate.impl.SessionImpl - closing session
DEBUG main org.hibernate.jdbc.ConnectionManager - closing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
DEBUG main org.hibernate.connection.DriverManagerConnectionProvider - returning connection to pool, pool size: 1
DEBUG main org.hibernate.jdbc.JDBCContext - after transaction completion
DEBUG main org.hibernate.impl.SessionImpl - after transaction completion

My feeling is that you're on the wrong track because you have a misconception of how transitive persistence works (see http://www.hibernate.org/hib_docs/v3/re ... transitive or http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#example-parentchild, noting that your example reverses the role of parent and child compared to the examples in the documentation).

Let us start with the set you declare in Child:

This doesn't make sense, because it means that you can delete a child without caring about his parents, which would therefore stay with a dangling foreign key to the deleted - of course this fails.

Now consider that what you expect means that each time a parent is deleted Hibernate must issue a select on the parent table to check that the deleted parent is the last one of the child it is related to. This is definitely costly.

And my opinion (I may be wrong) is that Hibernate does not support what you want out of the box - but it is easy to implement.

Erik

As I said, I am not sure the mapping is right, in fact it is probably wrong. But I know know how I want the relationship to behave, and I was hoping people could help me fix the mapping definition to get it to behave that way.

So I guess the only thing I can do is programatically go delete the children when all the parents are gone. I just though Hibernate would be able to figure out that the child still has another parent pointing to it.

Anyone else have any ideas?[/quote]