FOREIGN KEY constraint failed when trying to delete entity referenced in value object

Question

Domain of interest

I'm writing a POS software. It has a concept of money transfer means, some of which are physical.

Each physical means allows some denominations which are essentially a combination of a face value and a fiat currency. Example: 1 USD, 5 EUR, 10 CHF, etc.

A currency is a separate entity that lives (for other purposes) without any money transfer means. Denominations on the other hands are dependent on one money transfer mean.

Current state of implementation

So far, I've modeled with success this domain into my model

public abstract class FiatCurrency : AggregateRoot
{
    private IList<Denominations> Denominations { get; }
    public virtual Currency Currency { get; }

    public static class Expressions
    {
        public static readonly Expression<Func<FiatCurrency, IEnumerable<Denominations>>> Denominations = x => x.Denominations;
    }

    public FiatCurrency(Currency currency)
    {
        Currency = currency;
    }

    protected FiatCurrency() { }
}

public abstract class PhysicalMoneyTransferMean : MoneyTransferMean
{
    private readonly IList<Denominations> _denominations;
    public virtual IReadOnlyList<Denominations> Denominations => _denominations.ToList();

    public PhysicalMoneyTransferMean(MoneyTransferMeanDirection direction, IList<ForeignCurrency> extraCurrencies, IDictionary<FiatCurrency, List<FaceValue>> denominations)
        : base(direction, extraCurrencies)
    {
        _denominations = denominations.Select(kv => new Denominations(this, kv.Key, kv.Value)).ToList();
    }

    protected PhysicalMoneyTransferMean() { }
}

public class Denominations : Entity
{
    public virtual PhysicalMoneyTransferMean MoneyTransferMean { get; }
    public virtual FiatCurrency Currency { get; }
    private string FaceValuesInternal { get; }
    public virtual IReadOnlyList<FaceValue> FaceValues => FaceValuesInternal.Split(';').Select(f => new FaceValue(f)).ToList();

    public static class Expressions
    {
        public static readonly Expression<Func<Denominations, object>> FaceValues = x => x.FaceValuesInternal;
    }

    public Denominations(PhysicalMoneyTransferMean moneyTransferMean, FiatCurrency currency, IEnumerable<FaceValue> faceValues)
    {
        MoneyTransferMean = moneyTransferMean;
        Currency = currency;
        FaceValuesInternal = string.Join(";", faceValues);
    }

    protected Denominations() { }
}

The mappings:

public class FiatCurrencyMap : ClassMap<FiatCurrency>
{
    public FiatCurrencyMap()
    {
        Id(Entity.Expressions<FiatCurrency>.Id);
        Map(c => c.Currency)
            .CustomType<CurrencyUserType>();
        HasMany(FiatCurrency.Expressions.Denominations)
            .KeyColumn("CurrencyId")
            .Inverse()
            .Cascade.All()
            .Not.LazyLoad();
        DiscriminateSubClassesOnColumn("Type");
    }
}


public sealed class PhysicalMoneyTransferMeanMap : SubclassMap<PhysicalMoneyTransferMean>
{
    public PhysicalMoneyTransferMeanMap()
    {
        HasMany(x => x.Denominations)
            .KeyColumn("MoneyTransferMeanId")
            .Cascade.All()
            .Not.LazyLoad()
            .Access.CamelCaseField(Prefix.Underscore);
    }
}

public sealed class DenominationsMap : ClassMap<Denominations>
{
    public DenominationsMap()
    {
        Id(Entity.Expressions<Denominations>.Id);
        References(x => x.MoneyTransferMean);
        References(x => x.Currency);
        Map(Denominations.Expressions.FaceValues)
            .Column(nameof(Denominations.FaceValues));
    }
}

So far so good, the mapping above yields the following database structure (I'm on SQLite):

CREATE TABLE "FiatCurrency" (Id  integer primary key autoincrement, Type TEXT not null, Currency TEXT not null, ExchangeRateToLocalCurrency NUMERIC)

CREATE TABLE "MoneyTransferMean" (Id  integer primary key autoincrement, Type TEXT not null, Direction INT not null)

CREATE TABLE "Denominations" (Id  integer primary key autoincrement, FaceValues TEXT not null, MoneyTransferMeanId BIGINT not null, CurrencyId BIGINT not null, constraint FK_F2F07762 foreign key (MoneyTransferMeanId) references "MoneyTransferMean", constraint FK_B0AA916 foreign key (CurrencyId) references "FiatCurrency")

Problem

When deleting a money transfer mean from, NHibernate also successfully removes associated denominations, however when deleting a fiat currency I get the following ("obvious") message:

could not execute update query[SQL: delete from "FiatCurrency" where Type='Local' and ?=1] ---> System.Data.SQLite.SQLiteException: constraint failed FOREIGN KEY constraint failed

My question is how can I tell NHibernate to remove all denominations referencing a fiat currency when removing the fiat currency itself ?

Answer 1

You could use a Cascade Strategy using FluentNHibernate:

FluentNHibernate

 public class FiatCurrencyGroupMap : ClassMap<FiatCurrencyGroup>
 {
  public FiatCurrencyGroupMap()
  {
     Id(x => x.Id);
     HasMany<Denomination>(x => x.Denominations)
        .Cascade.All();
  }
}

As a workaround, you could also first delete the Denominations and then delete the FiatCurrency from the DB.