NHibernate Mapping problem

Question

My database is being driven by my NHibernate mapping files.

I have a Category class that looks like the following :

public class Category {

        public Category() : this("") { }

        public Category(string name) {
            Name = name;
            SubCategories = new List<Category>();
            Products = new HashSet<Product>();
        }


        public virtual int ID { get; set; }
        public virtual string Name { get; set; }
        public virtual string Description { get; set; }
        public virtual Category Parent { get; set; }
        public virtual bool IsDefault { get; set; }
        public virtual ICollection<Category> SubCategories { get; set; }
        public virtual ICollection<Product> Products { get; set; }   

and here is my Mapping file :

<property name="Name" column="Name" type="string" not-null="true"/>
<property name="IsDefault" column="IsDefault" type="boolean" not-null="true" />
<property name="Description" column="Description" type="string" not-null="true" />

<many-to-one name="Parent" column="ParentID"></many-to-one>

<bag name="SubCategories" inverse="true">
  <key column="ParentID"></key>
  <one-to-many class="Category"/>
</bag>
<set name="Products" table="Categories_Products">
  <key column="CategoryId"></key>
  <many-to-many column="ProductId" class="Product"></many-to-many>
</set>

when I try to create the database I get the following error :

failed: The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK9AD976763BF05E2A". The conflict occurred in database "CoderForTraders", table "dbo.Categories", column 'CategoryId'. The statement has been terminated.

I looked on the net for some answers but found none. Thanks for your help

Here is the missing part :

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="CBL.CoderForTraders.DomainModel" namespace="CBL.CoderForTraders.DomainModel" default-access="field.camelcase-underscore" default-lazy="true">

  <class name="Category" table="Categories" >
    <id name="_persistenceId" column="CategoryId" type="Guid" access="field" unsaved-value="00000000-0000-0000-0000-000000000000">
      <generator class="assigned" />
    </id>
    <version name="_persistenceVersion" column="RowVersion" access="field" type="int" unsaved-value="0" />

Answer 1

I think the problem is that you have to provide an explicit fk-constraint-name to avoid naming colision:

<bag name="SubCategories">
  <key column="ParentID" foreign-key="fk_Category_ParentCategory"/>
  <one-to-many class="Category"/>
</bag>

Here you can find a good tutorial on how to map a tree in NHibernate. Since it also uses schema generation it should solve your problem.

Answer 2

The error is occurring on an insert, not table creation. You don't show the mapping for ID, but if it's an identity column in SQL Server then it's impossible to insert the first record because a parent record (Category property) doesn't exist. One solution might be to temporarily drop the constraint, insert a "root" record that refers to itself, then add the constraint back.