Foreign Key Constraint is preventing NHibernate from saving child record

nhibernate

I have two tables:

SupportTicket

SupportTicketID
SupportTicketDate

SupportTicketNote

SupportTicketNoteID
SupportTicketNoteDate
SupportTicketID

With a foreign key constraint so I don't have any unassociated Notes…in sql that constraint is working properly.

On my SupportTicket class I have an IList SupportTicketNotes property and have it mapped as a bag (probably really should be a set but that's not important at the moment). The load works just fine. The problem is if I new up a SupportTicket, new up a SupportTicketNote, add the note to the ticket and save the ticket. NHibernate is inserting the SupportTicket, then inserting the SupportTicketNote with a SupportTicketID of zero which blows up of course because of the FK constraint. If I delete the constraint it will insert with the SupportTicketID of zero and then go back and do an update on the SupportTicketNote with the proper ID value…but that seems….wrong. Is there anything I might be doing in the mapping that is causing that?

UPDATED to include Many to One mapping on child object

Here's my current mapping for SupportTicket:

<bag name="_supportTicketNotes" table="SupportTicketNotes" access="field" cascade="save-update" inverse="true" >
  <key column="SupportTicketID" foreign-key="FK_SupportTicketNotes_supporttickets" ></key>
  <one-to-many class="NhibernateSample.DomainModel.SupportTicketNote, NhibernateSample.DomainModel" />
</bag>

Here is my mapping for SupportTicketNote (note my SupportTicketNote class has both the SupportTicketID and SupportTicket object properties):

 <many-to-one name="SupportTicket"   class="NhibernateSample.DomainModel.SupportTicket, NhibernateSample.DomainModel"   column="SupportTicketId" cascade="all"/>

Best Answer

I haven't seen your full mapping, but the first thing that pops into my head is this section from the documentation:

Very Important Note: If the <key> column of a <one-to-many> association is declared NOT NULL, NHibernate may cause constraint violations when it creates or updates the association. To prevent this problem, you must use a bidirectional association with the many valued end (the set or bag) marked as inverse="true". See the discussion of bidirectional associations later in this chapter.

Related Solutions

How to delete child object in NHibernate

You are getting the first error because, when you remove the items from the collection, NHibernate's default mode of operation is to simply break the association. In the database, NHibernate tries to set the foreign key column on the child row to null. Since you do not allow nulls in that column, SQL Server raises the error. Clearing the collection will not necessarily delete the child object, but one way to do so is to set cascade=all-delete-orphan. This informs NHibernate that it should delete the newly orphaned rows instead of setting the foreign key column.

You are getting the second error because when you call SaveOrUpdate NHibernate first deletes all of the child objects. Then, because neither relationship is marked as inverse, NHibernate also tries to set the foreign key column in your child table to null. Since the rows have already been deleted, you receive the second error. You need to set inverse=true on one side of your relationship to fix this. This is usually done on the one (primary key or parent) side. If you do not do this, NHibernate will make the appropriate updates for each side of the relationship. Unfortunately, running two updates is not the appropriate thing to do.

You should always mark one side of your relationships as the inverse side. Depending on how you code, you may or may not need to use cascading. If you want to take advantage of one shot deletes as you are trying to do using Clear(), you need to define your cascade.

Hibernate – When to use inverse=false on NHibernate / Hibernate OneToMany relationships

As Matthieu says, the only case where you wouldn't want to set inverse = true is where it does not make sense for the child to be responsible for updating itself, such as in the case where the child has no knowledge of its parent.

Lets try a real world, and not at all contrived example:

<class name="SpyMaster" table="SpyMaster" lazy="true">
  <id name="Id">
    <generator class="identity"/>
  </id>
  <property name="Name"/>
  <set name="Spies" table="Spy" cascade="save-update">
    <key column="SpyMasterId"/>
    <one-to-many class="Spy"/>
  </set>
</class>

<class name="Spy" table="Spy" lazy="true">
  <id name="Id">
    <generator class="identity"/>
  </id>
  <property name="Name"/>
</class>

Spymasters can have spies, but spies never know who their spymaster is, because we have not included the many-to-one relationship in the spy class. Also (conveniently) a spy may turn rogue and so does not need to be associated with a spymaster. We can create entities as follows:

var sm = new SpyMaster
{
    Name = "Head of Operation Treadstone"
};
sm.Spies.Add(new Spy
{
    Name = "Bourne",
    //SpyMaster = sm // Can't do this
});
session.Save(sm);

In such a case you would set the FK column to be nullable because the act of saving sm would insert into the SpyMaster table and the Spy table, and only after that would it then update the Spy table to set the FK. In this case, if we were to set inverse = true, the FK would never get updated.