Deleteing from a delete-orphan one-to-many collection

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

Problem: I am deleting an item from a simple collection. I'm using cascade="all-delete-orphan". Hibernate is doing this in two steps: first it removes the item from the collection (by updating the FK column to null) then it deletes the row. I have a non-null constraint on the FK column, so the first update is failing. Can I avoid doing that update? It's superfluous anyway, seeing as the delete-orphan part of the mapping ensures that the row is going to be deleted immediately after it is updated.

For testing purposes, I removed the not-null constraint and everything works. I can see the update immediately followed by the delete in hibernate's trace output.

Hibernate version: 3.0.5
Mapping documents: (excerpt)

Code:

    <set name="Terms" inverse="false" cascade="all-delete-orphan"     
         where="TypeID='DA1144DE-01AC-4671-89A1-45D33956D019'">
      <key column="PointID"/>
      <one-to-many class="TermImpl"/>
    </set>

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

Code:

      cp.getTerms().clear();
      factCP.save(cp);

Full stack trace of any exception that occurs:

Code:

org.hibernate.exception.ConstraintViolationException: could not delete collection: [com.energyintellect.manage.connectionpoint.impl.ConnectionPointImpl.IEMUserTerms#D4919281-1E6E-4361-BAF8-6A40039A5D26]
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:63)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.persister.collection.AbstractCollectionPersister.remove(AbstractCollectionPersister.java:860)
   at org.hibernate.action.CollectionUpdateAction.execute(CollectionUpdateAction.java:39)
   at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:223)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:730)
   at com.tml.framework.datasource.impl.FrameworkSessionWrapper.flush(FrameworkSessionWrapper.java:261)
   at com.energyintellect.manage.connectionpoint.impl.DefaultConnectionPointFactory.save(DefaultConnectionPointFactory.java:171)
   at com.tml.test.framework.manage.TestGENInterface.testClientTerm(TestGENInterface.java:229)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at junit.framework.TestCase.runTest(TestCase.java:154)
   at junit.framework.TestCase.runBare(TestCase.java:127)
   at junit.framework.TestResult$1.protect(TestResult.java:106)
   at junit.framework.TestResult.runProtected(TestResult.java:124)
   at junit.framework.TestResult.run(TestResult.java:109)
   at junit.framework.TestCase.run(TestCase.java:118)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: com.inet.tds.SQLException: Msg 515, Level 16, State 2, Line 1, Sqlstate 23000
[TML-DEV1]Cannot insert the value NULL into column 'ConnectionPointID', table 'Manage.dbo.ClientTerm'; column does not allow nulls. UPDATE fails.
   at com.inet.tds.a.a(Unknown Source)
   at com.inet.tds.a.a(Unknown Source)
   at com.inet.tds.b.try(Unknown Source)
   at com.inet.tds.b.executeUpdate(Unknown Source)
   at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
   at org.hibernate.persister.collection.AbstractCollectionPersister.remove(AbstractCollectionPersister.java:850)
   ... 23 more

Name and version of the database you are using:
SQLServer2000

The generated SQL (show_sql=true):

Code:

Hibernate: update ClientTerm set ConnectionPointID=null where ConnectionPointID=? and ClientTypeID='DA1144DE-01AC-4671-89A1-45D33956D019'

Debug level Hibernate log excerpt:

Code:

ERROR [org.hibernate.util.JDBCExceptionReporter] org.hibernate.util.JDBCExceptionReporter JDBCExceptionReporter.java:72 - [TML-DEV1]Cannot insert the value NULL into column 'ConnectionPointID', table 'Manage.dbo.ClientTerm'; column does not allow nulls. UPDATE fails.
ERROR [org.hibernate.event.def.AbstractFlushingEventListener] org.hibernate.event.def.AbstractFlushingEventListener AbstractFlushingEventListener.java:277 - Could not synchronize database state with session

jt_1000 · **Posted:** Tue Mar 14, 2006 7:38 pm

After experimenting a bit, I found that if you set the <key...not-null="true"> in the Parent's set, then
in the Childs <many-to-one set update="false" and insert="false". You are stating that all Children and thus relationships are derived from the Parent side of things. If setup this way, then no "update" is done before the delete. Of course now, you can't "insert/update" a Child record by itself, anymore.

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

That was it. It even says to do exactly this in the ref docs (section 6.2.5. "One-to-many associations"), I just forgot.

Point of interest here: after rewriting my stored proc to deal with hibernate's preference to put primary keys at the end of the parameter list (I have to use a stored proc for sql-insert for this table, there's a linked server involved, some data is being replicated to a completely unrelated DB), I had to change the parameter list again. When you set key not-null="true" (and the corresponding required update="false" insert="false" on the inverse association, the many-to-one in the Term table), the foreign key is moved to immediately before the primary key in the parameter list. This happens despite not changing the order of the mapping in the .cfg.xml file. Sigh. Still, it's working great now.

jt_1000 · **Posted:** Tue Mar 14, 2006 9:02 pm

great - glad I can help.

I never liked how the parameter list in stored-procs was just "derived" and the docs say to turn on debugging in order to get the correct order - it is good to keep what you found in mind. thx.