如何删除ForeignKey (Cascade Delete)

Question

我有两个模型，它们通过外键约束相互绑定。

1. PurchaseOrder为父表
PurchaseOrderQtyBreakDown是子表

现在的问题是，当我试图删除父表，这个错误发生:

SqlException: DELETE语句与REFERENCE约束"FK_POQtyBreakDown_PurchaseOrder_PurchaseOrderPoId"冲突。冲突发生在数据库infosys_commercialapi，表dbo. poqtybreakdown，列PurchaseOrderPoId

我尝试了以下方法来实现Cascade Delete。但是它不能正常工作。

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

PurchaseOrder&PurchaseOrderQtyBreakDown模型类如下所示:

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

请帮我解决这个问题。非常感谢。

Answer 1

我从微软文档中得到了一个解决方案。您只需要执行以下操作:

1. 进入数据库
2. 选择子表
<
3. 打开键/gh>
4. 选择想要"cascade"的FK关系
5. 右键选择修改
6. 选择INSERT And UPDATE Spec
7. 选择删除规则
8. 设置选项"cascade";
9. 并保存。

从现在开始您可以删除父行。