Adding a non-nullable foreign key reference

• Tweet
• Email

• Tweet
• Email

I started a new web site recently, so decided to try out ASP.NET Core. This of course brought EF Core with it, and the whole new (to me) world of code-first and data migrations.

This all went well, until today. I have a Donations table that was assumed to store donation information in GBP (being the only currency we supported in the first iteration). We now want to support multiple currencies.

No problem, we add a new Currency class, with virtual collection for the Donation…

public class Currency {
  public int Id { get; set; }
  public string Code { get; set; }
  public string Symbol { get; set; }
  public virtual IEnumerable<Donation> Donations { get; set; }
}

…and then modified the Donation class to link to it…

public class Donation {
  public int Id { get; set; }
  public int CurrencyID { get; set; }

  [ForeignKey(nameof(CurrencyID))]
  public virtual Currency Currency { get; set; }
}

I also added some code to populate the currencies…

  builder.Entity<Currency>().HasData(new List<Currency> {
    new Currency { Id = (int)SupportedCurrencies.GBP, Code="GBP", Symbol = "£" },
    new Currency { Id = (int)SupportedCurrencies.EUR, Code="EUR", Symbol = "€" },
    new Currency { Id = (int)SupportedCurrencies.USD, Code="USD", Symbol = "$" },
    //...
  });

I then added a migration, and tried to update the database. Sadly, this gave an error…

Cannot insert the value NULL into column ‘CurrencyID’, table ‘Donations’; column does not allow nulls. UPDATE fails.
The statement has been terminated.

This left me scratching my head a bit, as it looked like it was trying to update the table structure without adding the data. However, I needed the data adding before it modified the Donations table, so that the CurrencyID could be set.

Pre-Core, I would have added a Currencies table, then in an SQL script, populated it, then added the column to the Donations table, setting the CurrencyID property to be the ID of GBP for all existing donations. However, EF Core is all about code-first, and there doesn’t seem to be a way to do that.

To cut a long story short, the answer turned out to be annoyingly simple. Yes I had added the data, but I had forgotten to set a default value on the CurrencyID column of the Donations table. Therefore, even though the currency data had been added (I assume), none of the existing donations were being updated to use it, resulting in a null error.

All I needed to do was add the following…

builder.Entity<Donation>()
  .Property(p => p.CurrencyID)
  .HasDefaultValue(1);

This ensured that the existing donations had a valid CurrencyID, and the error went away…

…to be replaced with another! Go on, you could see that coming couldn’t you?

I now had the error…

Introducing FOREIGN KEY constraint ‘FK_Donations_Currencies_CurrencyID’ on table ‘Donations’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints

Thankfully this turned out to be fairly easy to solve. I just needed to modify the Up method in the migration, setting onDelete to Restrict rather than the default Default…

migrationBuilder.AddForeignKey(
    name: "FK_Donations_Currencies_CurrencyID",
    table: "Donations",
    column: "CurrencyID",
    principalTable: "Currencies",
    principalColumn: "Id",
    onDelete: ReferentialAction.Restrict);

All fairly obvious with hindsight.