Error: SQLite Error 19: 'FOREIGN KEY constraint failed'

Question

When I try to add data to the Order table, an error occurs.

Adding data worked and there were no errors in mapping, validation, and even more so in the database itself. But a day later, when I added the error trapping middleware and populated the Dish table, it stopped working. There were no changes in the structure of the tables or the code associated with them. For the second day I can not understand what I'm doing wrong and how it could even break.

The payment and delivery entities are already filled in the database. And I am using SQLite.

Error:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102] Failed executing DbCommand (13ms) [Parameters=[@p0='?' (DbType = Guid), @p1='?', @p2='?' (Size = 27), @p3='?', @p4='?' (Size = 4), @p5='?' (Size = 10), @p6='?' (DbType = DateTime), @p7='?' (DbType = Guid), @p8='?' (DbType = Boolean), @p9='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30'] INSERT INTO "Orders" ("Id", "Comment", "CustomerAddress", "CustomerMail", "CustomerName", "CustomerPhone", "Date", "DeliveryId", "IsCompleated", "PaymentId") VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9);

fail: Microsoft.EntityFrameworkCore.Update[10000] An exception occurred in the database while saving changes for context type 'Restaurant.Persistents.RestaurantDbContext'. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details. Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'FOREIGN KEY constraint failed'. at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db) at Microsoft.Data.Sqlite.SqliteDataReader.NextResult() at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken) End of inner exception stack trace --- at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList1 entriesToSave, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken) Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details. Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'FOREIGN KEY constraint failed'. at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db) at Microsoft.Data.Sqlite.SqliteDataReader.NextResult() at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken) End of inner exception stack trace --- at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList1 entriesToSave, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

If needed, I can add a link to the project git.

Entities and their configurations

Order

public class Order
    {
        // Primary key
        public Guid Id { get; set; }

        public bool? IsCompleated { get; set; }
        public DateTime Date { get; set; }
        public string CustomerName { get; set; }
        public string CustomerPhone { get; set; }
        public string? CustomerMail { get; set; }
        public string CustomerAddress { get; set; }
        public string? Comment { get; set; }

        // Foreign key
        public Guid DeliveryId { get; set; }
        public Guid PaymentId { get; set; }

        // Navigation property
        public List<Content> Contents { get; set; }
        public Payment Payment { get; set; }
        public Delivery Delivery { get; set; }
    }

Related entities Delivery and Payment are identical

Delivery

public class Delivery
    {
        // Primary key
        public Guid Id { get; set; }

        public string Title { get; set; }

        // Foreign key

        // Navigation property
        public List<Order> Orders { get; set; }
    }

Content

public class Content
    {
        // Primary key
        public Guid Id { get; set; }

        public int Number { get; set; }

        // Foreign key
        public Guid DishId { get; set; }
        public Guid OrderId { get; set; }

        // Navigation property
        public Dish Dish { get; set; }
        public Order Order { get; set; }
    }

Configuration files, omitted everything except the keys for convenience.

Order

public void Configure(EntityTypeBuilder<Order> builder)
        {
            builder.HasKey(order => order.Id);
            builder.HasIndex(order => order.Id)
                .IsUnique();

            builder.HasOne(order => order.Delivery)
                .WithMany(delivery => delivery.Orders)
                .HasForeignKey(order => order.DeliveryId);
            builder.HasOne(order => order.Payment)
                .WithMany(payment => payment.Orders)
                .HasForeignKey(order => order.PaymentId);
        }

Delivery

public void Configure(EntityTypeBuilder<Delivery> builder)
        {
            builder.HasKey(delivery => delivery.Id);
            builder.HasIndex(delivery => delivery.Id)
                .IsUnique();
        }

Content

public void Configure(EntityTypeBuilder<Content> builder)
        {
            builder.HasKey(content => content.Id);
            builder.HasIndex(content => content.Id)
                .IsUnique();

            builder.HasOne(content => content.Order)
                .WithMany(order => order.Contents)
                .HasForeignKey(content => content.OrderId);
            builder.HasOne(content => content.Dish)
                .WithMany(dish => dish.Contents)
                .HasForeignKey(content => content.DishId);
        }

I sent a query using postman and waited for a new record to be added to the table. But as a result, only Get requests work. Any attempts to change the database from the code have stopped working and give a secondary key error. But you can add it through DB Browser for SQLite. I am new to working with ef core. And I just can't figure out why this error occurs.

Answer 1

The error you are getting is a SQLite error and shouldn't be directly related to EF. With that in mind, if you try to insert a row containing a column which references another table, you have to make sure that the content of the column also exists in the referenced table. What you could do to debug this error is to open the SQLite database and run exactly the same INSERT statement with the same values as described in your error and see what happens.

So you'd run the following statement after replacing @p0, @p1 etc. with the values EF is trying to insert:

INSERT INTO "Contents" ("Id", "DishId", "Number", "OrderId") VALUES (@p0, @p1, @p2, @p3)