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 ?

Thomas Hahn · Answer 1 · 2020-01-24T10:52:17.233

2

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.

edited Jan 24 '20 at 10:52

answered Jan 24 '20 at 10:38

Thomas Hahn

171
1
2
12

As I said, I can't delete the FiatCurrency (foreign key constraint failed). – Spotted Jan 24 '20 at 10:40
Misunderstood you, I corrected my answer. First, delete the Denominations, then delete the FiatCurrency. Or try using the FluentNHibernate to introduce a Cascade Strategy. – Thomas Hahn Jan 24 '20 at 10:43
Can you expand a bit on cascade strategies ? Is it related to the DB doing the `cascade delete` or NHibernate with `.Cascade.Delete()` (in this case, how am I supposed to alter the mapping) ? – Spotted Jan 24 '20 at 10:48
@spotted I added example code in my answer. I could not test it yet, but you should get the concept. You tell NHibernate to cascade delete all, when fiat currency is removed. – Thomas Hahn Jan 24 '20 at 10:53
Thank you for your insight. I went and created a separate `Denominations` entity however the problem remains (see my updated question). – Spotted Jan 24 '20 at 13:47

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

Domain of interest

Current state of implementation

Problem

1 Answers1