Cascading delete throuh map - null column error

Falken224 · **Joined:** Thu Jan 05, 2006 3:59 pm **Posts:** 10

I've been posting some pretty obscure questions or errors lately. Hopefully this one's a bit more straightforward.

I have accounts and countries which are related many-to-many through an AccountCountryInfo table (which contains more data about the relation)

I have an Account object, An AccountCountryInfo object, and a Country object. The collection on Account of the AccountCountryInfo objects is a map, since there's only one AccountCountryInfo object per Country, and The country's PK is the key to the map. Here's my mapping for the Account object. (minus irrelevant details)

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="Account,MyAssembly table="Account">

      <id name="PrimaryKey" column="AccountId" type="Int32" unsaved-value="-1">
         <generator class="native"/>
      </id>
      <map name="iAccountCountryInfoByCountryIdList" access="field" lazy="true" cascade="all">
         <key column="AccountId" />
         <index column="CountryId" type="Int32"/>
         <one-to-many class="AccountCountryInfo,MyAssembly"/>
      </map>
      <!-- other irrelevant properties omitted-->
   </class>
</hibernate-mapping>

And the mapping for the AccountCountryInfo object

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="DAOAccountCountryInfo,MyAssembly" table="AccountCountryInfo">

      <id name="PrimaryKey" column="AccountCountryInfoId" type="Int32" unsaved-value="-1">
         <generator class="native"/>
      </id>
      <many-to-one name="Account" column="AccountId" class="DAOAccount,MyAssembly" not-null="true"/>
      <many-to-one name="Country" column="CountryId" class="DAOCountry,MyAssembly" not-null="true"/>
   <!-- Again . . . irrelevant info deleted -->
   </class>
</hibernate-mapping>

My assumption is that when I delete an account, it cascades that down through the AccountCountryInfo collection deleting them one by one. Indeed, it works on all the <bag> collections I have set up for account. But . . . I get the following exception when I try to delete the Account object

Code:

[SqlException (0x80131904): Cannot insert the value NULL into column 'CountryId', table 'MyDB.dbo.AccountCountryInfo'; column does not allow nulls. UPDATE fails.
The statement has been terminated.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857466
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735078
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
   NHibernate.Impl.NonBatchingBatcher.AddToBatch(Int32 expectedRowCount) +38
   NHibernate.Collection.AbstractCollectionPersister.Remove(Object id, ISessionImplementor session) +185

[ADOException: could not delete collection: [MyAssembly.DAOAccount.iAccountCountryInfoByCountryIdList#1828]]
   NHibernate.Collection.AbstractCollectionPersister.Remove(Object id, ISessionImplementor session) +288
   NHibernate.Impl.ScheduledCollectionRemove.Execute() +30
   NHibernate.Impl.SessionImpl.Execute(IExecutable executable) +85
   NHibernate.Impl.SessionImpl.ExecuteAll(IList list) +83
   NHibernate.Impl.SessionImpl.Execute() +176
   NHibernate.Impl.SessionImpl.Flush() +33
   NHibernate.Transaction.AdoTransaction.Commit() +142
   MyAssembly.Hibernate.HibernateUnitOfWork.conclude() in <my source code path>\HibernateCore.cs:1249

What it LOOKS like is that something to do with removing the object from the collection before the delete is trying to null out my CountryId column BEFORE deleting.the AccountCountryInfo object. Right now, I'm manually removing all the AccountCountryInfo objects from the collection and deleting them one by one, just to make the code work, but if I have a cascade set up, it seems like it ought to work even on a map where the index is a non-nullable column.

Or is there something else at work here that I'm missing?

Any help is greatly appreciated.

Thank you much.

-Falken

manuj · **Joined:** Sun Jul 24, 2005 2:19 am **Posts:** 7

I have very similar problem.

Is this a bug? Why would NH try to null out the index column of a map?

More importantly - since we are only interested in deleting the objects - why go through the trouble of sending the updates to the database to nullify these columns? Can't NH determine orphans by just setting the references in memory to null and then send the resulting delete statements to the database?

abombss · **Posted:** Sat Mar 11, 2006 6:11 pm

I have the same issue with trying to cascade deletes as well. NHibernate first sends the update to null out the association key, then sends the delete statement to delete the whole associated record. While not in good form, my workaround was to allow nulls on the associated key column. Outside of first setting the field to null NHibernate appears to be managing the relationships just fine.

Not sure if this is a bug or not, but maybe.

mteper · **Joined:** Wed Jun 08, 2005 4:59 pm **Posts:** 27

Could someone from the NH team respond? It hardly seems right to have to allow the index column to be nullable.

aogail · **Joined:** Sat Jun 03, 2006 2:26 pm **Posts:** 1

I'm having the same issue. I've tried various combinations of cascade styles, to no avail. Has anyone found a solution for this, besides allowing nulls?

Thanks,
Ben

devonl · **Posted:** Mon Jun 05, 2006 11:28 am

have you tried setting "inverse=true" on the collection side of the association?

grennis · **Joined:** Sat Mar 25, 2006 9:16 am **Posts:** 150

Isnt cascade supposed to be set to cascade="all-delete-orphan" ?

adressin · **Joined:** Wed Apr 07, 2004 6:13 pm **Posts:** 18

Anyone know what the resolution is for this? I am having the same issue: cascade="all" or cascade="delete", but when I delete my "parent" object, it attempts to update the key column of the associated table.

UPDATE: I fixed this by putting the inverse="true" as mentioned above.

Thanks!