Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

NHibernate sets Foreign Key in secondary update rather than on initial insert violating Not-Null Constrain on key column

I am having a problem with what should be a fairly simple (I would think) NHibernate use case.

I have a classic Parent and a Child entity like so:

public class Parent 
{
    public virtual int ParentId { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<Child> Children { get; set; }
}

public class Child
{
    public virtual int ChildId { get; set; }
    public virtual Parent Parent { get; set; }
    public virtual string Name { get; set; }
}

And mappings as follows:

public class ParentMap : ClassMap<Parent>
{
    public ParentMap()
    {
        Id(x => x.ParentId).GeneratedBy.Native();
        Map(x => x.Name);
        HasMany(x => x.Children).KeyColumn("ParentId").Cascade.SaveUpdate();
    }
}

public class ChildMap : ClassMap<Child>
{
    public ChildMap()
    {
        Id(x => x.ChildId).GeneratedBy.Native();
        Map(x => x.Name);
        References(x => x.Parent).Column("ParentId").ReadOnly().Not.Nullable();
    }
}

Lastly, I have a simple tests:

   [Test]
    public void Test_save_family()
    {
        var parent = new Parent();
        var child = new Child {Parent = parent};
        parent.Children = new List<Child>{child};

        SessionManager.WithSession(
            session =>
                {
                    session.Save(parent);
                    session.Flush();
                });

    }

The test fails with a System.Data.SqlClient.SqlException : Cannot insert the value NULL into column 'ParentId'. This is correct in that the column is non-nullable but why is it inserting null?

If I remove the null constraint, the save works because NHibernate first inserts the parent, then inserts the child, then updates the ParentId column on the child record as shown in this output:

NHibernate: INSERT INTO [Parent] (Name) VALUES (@p0); select SCOPE_IDENTITY();@p0 = NULL
NHibernate: INSERT INTO [Child] (Name) VALUES (@p0); select SCOPE_IDENTITY();@p0 = NULL
NHibernate: UPDATE [Child] SET ParentId = @p0 WHERE ChildId = @p1;@p0 = 2, @p1 = 1

This seems bizarre to me as in almost all cases foreign key columns of this sort are declared non-nullable and hence the foreign key must be provided at insert. So why is NHibernate not setting the foreign key on the initial insert of the child row and how to I fix this?

like image 356
Kenneth Baltrinic Avatar asked Nov 30 '10 16:11

Kenneth Baltrinic


People also ask

Why do foreign key constraints fail?

The error message itself showing there is a foreign key constraint error, which means you are deleting a parent table where the child table contains the Primary table identifier as a foreign key. To avoid this error, you need to delete child table records first and after that the parent table record.

What is foreign key mismatch?

Mismatched data types When you're using a foreign key to reference a column in another table, the datatypes of both tables have to be the same. For example, if the referencing column where you're declaring the foreign key uses the INT data type, then the referenced column must be INT as well.

What problems do foreign keys introduce?

Foreign key problems. Many database users encounter foreign key errors, often due to referential integrity problems. A foreign key might point to data that no longer exists, or the foreign key's data type doesn't match the primary key data type, eroding referential integrity.

Do foreign keys have to be the same type?

The structure and data type of PRIMARY KEY and FOREIGN KEY must be same. The values of the FOREIGN KEY columns in each row of the referencing table have to match with the values of the corresponding primary key columns of a row in the referenced table.


1 Answers

A few problems with your mapping... You have a bidirectional relationship and NHibernate needs to know which way to update it. In the OO world, references only go one way and there is no way for NHibernate to know that Parent->Children is the same FK as Child->Parent. Right now you have Child->Parent set to ReadOnly(). This is telling NHibernate not to update this property. So it tries to insert the Child (with a null parent) and then update the FK from the Parent side. This isn't working if you have a not null constraint on your FK. The usual way to map this is to use Inverse=true on the parent side and let the child worry about persistence. (It's your job in the OO model to ensure that the Parent->Children collection contains the same set of references as the set of Child->Parent relationships.)

public class ParentMap : ClassMap<Parent>
{
    public ParentMap()
    {
        Id(x => x.ParentId).GeneratedBy.Native();
        Map(x => x.Name);
        HasMany(x => x.Children).KeyColumn("ParentId").Inverse().Cascade.SaveUpdate();
    }
}

public class ChildMap : ClassMap<Child>
{
    public ChildMap()
    {
        Id(x => x.ChildId).GeneratedBy.Native();
        Map(x => x.Name);
        References(x => x.Parent).Column("ParentId").Not.Nullable();  // Removed ReadOnly()
    }
}

The SQL statements sent to the database when saving are now:

INSERT INTO [Parent]
           (Name)
VALUES     ('P1' /* @p0 */)
select SCOPE_IDENTITY()

INSERT INTO [Child]
           (Name,
            ParentId)
VALUES     ('C1' /* @p0 */,
            1 /* @p1 */)
select SCOPE_IDENTITY()
like image 74
James Kovacs Avatar answered Nov 14 '22 22:11

James Kovacs