NHibernate One-To-Many Delete Not Cascading

Question

I have a 'Photo' class and a 'Comment' class. An Photo can have multiple comments assigned to it.

I have this configured as a one-to-many relationship within my HBM mapping file, and have set cascade="all-delete-orphan" against the 'Comments' bag within the Photo.hbm.xml mapping file.

However, if I try to delete a Photo which has 1 or more Comments associated with it, I am getting 'The DELETE statement conflicted with the REFERENCE constraint "FK_Comments_Photos"'

I tried a couple of other cascade options against the Comments bag in my Photo.hbm.xml but regardless of what I set it to, I'm getting the same outcome each time. I just want to be able to delete a Photo and have any associated comments automatically delete too.

Here is my Photo mapping (edited for brevity):

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" .... default-access="property" default-cascade="none" default-lazy="true">
<class xmlns="urn:nhibernate-mapping-2.2" name="Photo" table="Photos">
    <id name="PhotoId" unsaved-value="0">
        <column  name="PhotoId" />
        <generator class="native" />
    </id>
    ...
    <bag name="Comments" table="Comments" cascade="all-delete-orphan" order-by="DateTimePosted desc" where="Approved=1">
        <key column="PhotoId" />
        <one-to-many class="Comment" />
    </bag>
</class>

Here is my Comment mapping (edited for brevity):

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" ... default-access="property" default-cascade="none" default-lazy="true">
<class xmlns="urn:nhibernate-mapping-2.2" name="Comment" table="Comments">
    <id name="CommentId" unsaved-value="0">
        <column name="CommentId"></column>
        <generator class="native" />
    </id>
    ...
    <property name="Author" not-null="true" />
    <property name="Body" not-null="true" />
    <property name="Approved" not-null="true" />
    <many-to-one name="Photo" not-null="true">
        <column name="PhotoId" />
    </many-to-one>
</class>

Does anyone have any suggestions as to why the cascade is not happening when I try to delete a Photo with comments associated with it?

UPDATE: The only way I can get the cascade to happen is to configure the 'Delete Rule' within SQL Server against this relationship to 'Cascade', and in doing so means that I don't need to specify any cascade action within my NHibernate Mapping. However, this isn't ideal for me - I'd like to be able to configure the cascade behaviour within the NHibernate Mapping ideally, so I'm still confused as to why it doesn't appear to be taking any notice of my NHibernate cascade setting?

Answer 1

My guess would be that the problem comes from the fact that the many-to-one in the Comment mapping is set to not-null="true". Because of that, NHibernate is not allowed to set this property to null temporarily before it deletes the Photo object and therefore when is goes about deleting the Photo object SQL Server throws an foreign key exception.

If I remember correctly for the order of actions when deleting is:

1. Set foreign key value to null in all child objects
2. Delete parent object
3. Delete all child references

Try to remove the not-null="true" from the many-to-one and see what will happen.