Fluent NHibernate: How are foreign keys set with HasMany, inserted or updated?

Question

A little context:

I have many instances of SomeCollection which store many Rule information that needs to be persisted to the database. Each Rule in the database have an auto incremented ID (PK) and a custom RuleId. The Rule table also contaisn a foreign key, which is the ID of the collection. The Rule table contains a unique constraint on both RuleId and CollectionId.

---

Question:

1. How are foreign keys inserted into tables when mapped with HasMany, is it updated after the child is inserted? I ask as null is shown in the messages below, but I know the values are set

2. If the above is true or false, how can I enforce multi-column unique constraint and avoid the issue detailed below.

When I set enforce the unique constraint in SomeCollectionMapping, I get the following violation:

FCE detected: System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'UQ__supporte__4D7ABD34DC8C8A20'. Cannot insert duplicate key in object 'dbo.rules'. The duplicate key value is (123, NULL).

The data is try inserting is:

123, myId1 
123, myId2

If I omit the lines: part.Not.Nullable() and part.UniqueKey("uniqueRef") the data is successfully inserted into both tables, but doesn't enforce uniqueness.

Even though the insert using Fluent NHibernate fails, I'm able to insert data using Microsoft SQl Server Management Studio without issues, and the multi-column constraint is working fine.

---

---

SomeCollection.cs

internal class SomeCollection
{
    public SomeCollection()
    {
        this.CollectionId = string.Empty;
        this.CollectionName = string.Empty;
        this.Rules = new List<Rule>();
    }

    public virtual string CollectionId { get; set; }

    public virtual string CollectionName { get; set; }

    public virtual IList<Rule> Rules { get; set; }
}

Rule.cs

internal class Rule
{
    public Rule()
    {
        this.DisplayName = string.Empty;
        this.RuleId = string.Empty;
        this.MyValue = 0;
    }

    public virtual string DisplayName { get; set; }

    public virtual string RuleId { get; set; }

    public virtual int MyValue { get; set; }
}

SomeCollectionMapping.cs

internal sealed class SomeCollectionMapping : ClassMap<SomeCollection>
{
    private const string TableName = "some_collections";

    public SomeCollectionMapping()
    {
        this.Table(TableName);

        this.Id(x => x.CollectionId)
            .Not.Nullable()
            .Column("collectionId")
            .UniqueKey("abc123");

        this.Map(x => x.CollectionName)
            .Column("name")
            .Not.Nullable();

        this.HasMany<Rule>(x => x.Rules)
            .KeyColumns.Add("collectionId", part =>
            {
                // No violations if I omit these two lines
                part.Not.Nullable();
                part.UniqueKey("uniqueRef");
            })
            .Cascade.All();
    }
}

RuleMapping.cs

internal sealed class RuleMapping : ClassMap<Rule>
{
    private const string TableName = "rules";

    public RuleMapping()
    {
        this.Table(TableName);

        this.Id().GeneratedBy.Increment().Unique();

        this.Map(x => x.RuleId)
            .Not.Nullable()
            .UniqueKey("uniqueRef")
            .Column("ruleId");

        this.Map(x => x.DisplayName)
            .Column("name")
            .Not.Nullable();

        this.Map(x => x.MyValue)
            .Column("myValue")
            .Not
            .Nullable();
    }
}

Nhibernate 4.1.1 FluentNHibernate 2.0.3.0 SQLServer

Answer 1

Right.

After randomly trying things, i added Not.KeyNullable() to the HasMany chain. The data is now correctly inserted and no exceptions are thrown. The unique and not null constraints are working fine.

This appears to be a fix, but if someone else wants to answer with more detail, I will gladly receive and accept those instead.