配置Cascade Delete后删除父表仍触发外键冲突的解决方法

我有两个通过外键约束关联的模型：

• PurchaseOrder 为父表
• PurchaseOrderQtyBreakDown 为子表

删除父表数据时触发如下错误：

SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_POQtyBreakDown_PurchaseOrder_PurchaseOrderPoId". The conflict occurred in database "InfoSys_CommercialAPI", table "dbo.POQtyBreakDown", column 'PurchaseOrderPoId'

已尝试通过EF配置级联删除但未生效，相关代码如下：

DbContext 代码 #

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
    }

    // Commercial Tables
    // Back-To-Back
    public DbSet<PurchaseOrder> PurchaseOrder { get; set; }
    public DbSet<PurchaseOrderQtyBreakDown> POQtyBreakDown { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<PurchaseOrderQtyBreakDown>()
            .HasOne(s => s.PurchaseOrder)
            .WithMany(b => b.PurchaseOrderQtyBreakDown)
            .HasForeignKey(b => b.PurchaseOrderPoId)
            .OnDelete(DeleteBehavior.Cascade);

        base.OnModelCreating(modelBuilder);
    }
}

模型类代码 #

public class PurchaseOrder
{
    [Key]
    public int PoId { get; set; }
    [DisplayName("CONTRACT")]
    public int? ContractListId { get; set; }
    [ForeignKey("ContractListId")]
    [ValidateNever]
    public ContractList? ContractList { get; set; }
    [DisplayName("ORDER NO")]
    [ValidateNever]
    public string? OrderNo { get; set; }
    public virtual List<PurchaseOrderQtyBreakDown> PurchaseOrderQtyBreakDown { get; set; } = new List<PurchaseOrderQtyBreakDown>();
}

public class PurchaseOrderQtyBreakDown
{
    public int Id { get; set; }

    public int? PurchaseOrderPoId { get; set; }
    [ForeignKey("PurchaseOrderPoId")]
    [ValidateNever]
    public PurchaseOrder? PurchaseOrder { get; set; }

    public int? ContractListId { get; set; }
    [ForeignKey("ContractListId")]
    [ValidateNever]
    public ContractList? ContractList { get; set; }

    public int? Qty { get; set; }

    public decimal? UnitPrice { get; set; }

    public string? OrderNo { get; set; }
}

解决方法 #

1. 手动更新数据库外键约束
   EF的OnDelete(DeleteBehavior.Cascade)配置如果是在数据库已创建后添加的，不会自动同步到现有数据库。需要手动执行SQL修改约束：

   -- 删除旧约束
   ALTER TABLE dbo.POQtyBreakDown
   DROP CONSTRAINT FK_POQtyBreakDown_PurchaseOrder_PurchaseOrderPoId;
   
   -- 添加带级联删除的新约束
   ALTER TABLE dbo.POQtyBreakDown
   ADD CONSTRAINT FK_POQtyBreakDown_PurchaseOrder_PurchaseOrderPoId
   FOREIGN KEY (PurchaseOrderPoId) REFERENCES dbo.PurchaseOrder(PoId)
   ON DELETE CASCADE;
   

2. 确保关联数据被加载
   如果暂时无法修改数据库，可以在删除父记录前显式加载关联的子数据，让EF自动处理子记录删除：

   var targetPO = await _context.PurchaseOrder
       .Include(p => p.PurchaseOrderQtyBreakDown)
       .FirstOrDefaultAsync(p => p.PoId == targetPoId);
   
   if (targetPO != null)
   {
       _context.PurchaseOrder.Remove(targetPO);
       await _context.SaveChangesAsync();
   }
   

3. 检查并应用EF迁移
   若使用EF迁移，需确保级联删除的配置已生成迁移并应用：

   # 生成迁移文件
   Add-Migration AddCascadeDeleteForPOQtyBreakDown
   # 应用到数据库
   Update-Database
   

4. 验证外键字段配置
   确认子表PurchaseOrderPoId的类型与父表PoId匹配（均为int/int?），且导航属性关联正确。如果子表外键允许为空，需确保待删除父记录对应的子记录外键不为空（空外键不会触发约束冲突，但也不会被级联删除）。

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