Not able to delete the foreign key contraint in Nhibernate Mappings

Question

public class ReviewMap : ClassMapBase<Review>
    {
        public ReviewMap()
        {
            Table("Reviews");

            List(x => x.Objectives, m =>
            {
                m.Table("Objectives");
                m.Cascade(Cascade.All.Include(Cascade.DeleteOrphans));
                m.Key(k => k.Column("ReviewId"));
                m.Where("DeletionDate is null");
                m.OrderBy("OrderNo");
                m.Index(x => x.Column("OrderNo"));
                m.Inverse(true);
            }, m => m.OneToMany());
        }


public class ObjectiveMap : ClassMapBase<Objective>
    {
        public ObjectiveMap()
        {
            Table("Objectives");

            // Use "soft deletes" to allow us to capture results against deleted items
            Where("DeletionDate is null");
            SqlDelete("update Objectives set DeletionDate = getutcdate(), OrderNo = 1 where id=?");

            ManyToOne(x => x.Review, m =>
            {
                m.Column("ReviewId");
                m.NotNullable(true);
            });

            Property(x => x.Details, m =>
            {
                m.NotNullable(true);
                m.Length(1000);
                m.Column("Details");
            });

            Property("OrderNo", m =>
            {
                m.NotNullable(false);
            });

        }
    }

When I try to delete the Reviews then It gives me the error for Foreign key constraint in Objective table. as ReviewId is foreign key in Objectives table.

Can you suggest what is the problem ?

Answer 1

You prevent deletions in the Objectives table with your custom sql-delete but also require that all Objectives have a reference to a Review row. How should this work? This would also require that Reviews are never deleted.

A way this can work would be if you remove the foreign key constraint on the Objectives.ReviewId column, but I wouldn't recommend that. Since the ID of a deleted Review row won't help you for history purposes, a better way would be to make the Objectives.ReviewId column nullable and also null it out in your custom sql-delete.