Foreign Key constraint conflict Parent Child using NHibernate

This was a tough one. I got everything working just fine with the mappings and my classes, I even wrote some test code to check if the Parent Child data was being inserted ok. What I failed to do was create the FOREIGN KEY relationship between the Parent table and the Child table. Once I did this I immediately started receiving the below error:

  • Die INSERT-Anweisung steht in Konflikt mit der FOREIGN KEY-Einschr√§nkung ‘FK_TBL1_TBL2’. Der Konflikt trat in der ‘**’-Datenbank, Tabelle ‘dbo.**’, column ‘ID’ auf. Die Anweisung wurde beendet.
  • Error: INSERT statement conflicted with COLUMN FOREIGN KEY constraint ‘FK_TBL1_TBL2’. The conflict occurred in database ‘**’, table ‘dbo.**’, column ‘ID’. The statement has been terminated.

This really through me for a loop and it took some time to find a solution for it. What I did not fully comprehend was the Parent and Child table relationship was built using a composite key. This is a very common design principle where the primary key is comprised of more than a single column, i.e. a unique id and the primary key from another table.

Basically, I used the composite-element to finally get it working.

<bag name="TheChildren" table="CHILD">
   <key column="PARENTID" />
   <composite-element class="ParentChild.Child">
     <property     name="Description"
                     column="DESCRIPTION"
                     type="string"  />
   </composite-element>
</bag>

This was very different to what I had been doing initially where I created a one-to-many relationship to the CHILD in the PARENT mapping file and a many-to-one relationship to the PARENT in the CHILD mapping.

A detailed description of how I implemented this can be found in my book.