Cannot add or update a child row: a foreign key constraint fails MYSQL : EF Core

Question

Getting a DbUpdateException when trying to insert data in MySQL database. Have used Pomelo.EntityFrameworkCore.MySql package to scaffold the models from the database, the project is created in .net core 2.1

StackTrace:

Exception message: Cannot add or update a child row: a foreign key constraint fails (HMEBooking.Invoice, CONSTRAINT Invoice_ibfk_7 FOREIGN KEY (BookingId) REFERENCES Booking (Id) ON DELETE NO ACTION ON UPDATE NO ACTION) Stack trace: at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 93 at MySql.Data.MySqlClient.MySqlDataReader.ReadFirstResultSetAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 328 at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(MySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 313 at MySqlConnector.Core.TextCommandExecutor.ExecuteReaderAsync(String commandText, MySqlParameterCollection parameterCollection, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\TextCommandExecutor.cs:line 73 at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 168 at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)

Have Booking, BookingDetail and Invoice models. BookingDetail has relation with Booking, Invoice has relation with Booking and BookingDetail.

While adding entries to the database getting the error Cannot add or update a child row: a foreign key constraint fails. But when I have the same models in a non .net core application it works

Kindly let me know if i am getting wrong somewhere. Thank you for the help.

Below are the models which are used

public partial class Booking
{
    public Booking()
    {
        BookingDetail = new HashSet<BookingDetail>();
        Invoice = new HashSet<Invoice>();
    }

    [Column(TypeName = "bigint(20)")]
    public long Id { get; set; }
    [Required]
    [Column(TypeName = "varchar(50)")]
    public string TempId { get; set; }

    [InverseProperty("Booking")]
    public ICollection<BookingDetail> BookingDetail { get; set; }
    [InverseProperty("Booking")]
    public ICollection<Invoice> Invoice { get; set; }
}
public partial class BookingDetail
{
    public BookingDetail()
    {
        Invoice = new HashSet<Invoice>();
    }

    [Column(TypeName = "bigint(20)")]
    public long Id { get; set; }
     [Required]
    [Column(TypeName = "varchar(50)")]
    public string CreatedBy { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime CreatedOn { get; set; }
    [Required]
    [Column(TypeName = "varchar(50)")]
    public string ModifiedBy { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime ModifiedOn { get; set; }
    [Column(TypeName = "bigint(20)")]
    public long BookingId { get; set; }       
    [ForeignKey("BookingId")]
    [InverseProperty("BookingDetail")]
    public Booking Booking { get; set; }
    [InverseProperty("BookingDetail")]
    public ICollection<Invoice> Invoice { get; set; }
}
 public partial class Invoice
{

    [Column(TypeName = "bigint(20)")]
    public long Id { get; set; }
    [Column(TypeName = "bigint(20)")]
    public long BookingId { get; set; }
    [Column(TypeName = "bigint(20)")]
    public long BookingDetailId { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime CreatedOn { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime CreatedBy { get; set; }
    [Column(TypeName = "datetime")]
    public DateTime ModifiedOn { get; set; }
    [Required]
    [Column(TypeName = "varchar(50)")]
    public string ModifiedBy { get; set; }
    [ForeignKey("BookingId")]
    [InverseProperty("Invoice")]
    public Booking Booking { get; set; }
    [ForeignKey("BookingDetailId")]
    [InverseProperty("Invoice")]
    public BookingDetail BookingDetail { get; set; }
}
  public class Class1
{
    HMEBookingContext context = new HMEBookingContext();
    #region declarations
    public long BookingId { get; set; }
    public string CustomerEmailId { get; set; } = "[email protected]";      
    #endregion
    Random rnd = new Random();
    public void insert()
    {
        try
        {
            Booking booking = new Booking();
            booking.TempBookingId = BookingRefNumber.ToString();

            var bookingDetail = new List<BookingDetail>();
            BookingDetail detail = new BookingDetail();
            detail.CreatedBy = CustomerEmailId;
            detail.CreatedOn = DateTime.Now;
            detail.ModifiedBy = "1";
            detail.ModifiedOn = DateTime.UtcNow;
            detail.BookingStatusId = 1;

            var invoices = new List<Invoice>();
            Invoice invoice = new Invoice();
            invoices.Add(invoice);
            detail.Invoice = invoices;



            bookingDetail.Add(detail);

            booking.BookingDetail = bookingDetail;

            context.Add(booking);
            context.SaveChanges(true);
            BookingId=booking.Id
        }
        catch(DbUpdateException upd)
        {


        }

        catch(Exception ex)
        {

        }
    }



}

Have one to many relation between booking -bookingDetail, booking-invoice, bookingDetail - invoice

EF Core version: 2.1 Database Provider: Pomelo.EntityFrameworkCore.MySql IDE: Visual Studio 2017

Answer 1

The error is that an Invoice record has an invalid value for Invoice.BookingId.

Looking at your test code, you create a single Invoice object, invoice; you never assign the invoice.Booking property nor do you add invoice to the booking.Invoices collection, so there is no relationship b/t the two established, thus the default value for Invoice.BookingId is being used and that PK value does not exist in table Booking.

Assign booking to invoice.Booking or add invoice to booking.Invoices and this error should go away.

This solution, however, does not correct a design flaw - an Invoice references both Bookings and BookingDetails and BookingDetails can then reference a different Booking record leading to data inconsistency. A single functional dependency should exist for Invoice, either Invoice -> BookingDetail or Invoice -> Booking, not both.