向SQLite的Order表插入数据时FOREIGN KEY约束失败问题排查

此前Order表的数据添加功能正常，映射、验证及数据库本身均无问题，但添加错误捕获中间件并填充Dish表后，所有代码层面的数据库修改操作均失败，返回外键约束错误，表结构及相关代码未做修改。目前已在SQLite数据库中填充了Payment和Delivery实体，仅Get请求正常，通过DB Browser for SQLite可正常添加数据。

错误信息 #

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)

实体及配置代码 #

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; }
}

Delivery实体（Payment实体结构一致） #

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

    public string Title { get; set; }

    // 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; }
}

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);
}

针对SQLite外键约束失败的问题，按以下步骤排查：

1. 验证外键值的有效性 #

• 检查Postman请求体中传入的DeliveryId和PaymentId，是否与数据库中已存在的Delivery、Payment记录的Id完全匹配（Guid区分大小写，格式必须一致）
• 确认填充的Delivery、Payment数据已持久化到数据库，没有因为事务回滚等原因未保存

2. 检查EF Core上下文的实体跟踪状态 #

• 如果创建Order时直接赋值DeliveryId/PaymentId，确保对应的Delivery/Payment实体已被EF Core上下文跟踪，或者显式调用_context.Attach(deliveryEntity)将实体附加到上下文
• 若使用导航属性关联（比如order.Delivery = existingDelivery），确保该Delivery实体是从当前上下文查询得到的，而非手动创建的新对象

3. 排查Content实体的关联问题 #

• 如果请求中同时创建了Content记录，检查DishId是否存在于Dish表中，OrderId是否与新创建的OrderId一致
• 建议通过Order的导航属性添加Content（order.Contents.Add(content)），让EF Core自动处理外键关联和保存顺序

4. 确认SQLite外键约束配置 #

• 检查数据库连接字符串是否包含Foreign Keys=True，SQLite需要显式开启外键约束
• 尝试调用_context.SaveChangesAsync(true)，强制接受所有更改并触发约束检查

内容的提问来源于stack exchange，提问作者TahaDMP