更新数据库时出现级联删除循环错误的原因及解决求助

错误信息 #

Error Number:1785,State:0,Class:16
Introducing FOREIGN KEY constraint 'FK_Products_Categories_CategoriesId' on table 'Products' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.

问题分析 #

你的实体关系中存在多条级联删除路径，导致SQL Server拒绝创建约束：

• 删除用户时，路径1：Users → Products → Transactions
• 删除用户时，路径2：Users → Categories → Products → Transactions
• 删除用户时，路径3：Users → Transactions

SQL Server不允许这种多路径级联删除，即使你已经修改了Products的两个外键，Categories与Users的关联、Transactions与Users/Products的关联可能仍默认使用级联删除，从而触发错误。

实体类与当前配置 #

Products类 #

public class Products : ITimeAuditable
{
    [Key]
    [Required]
    public string Id { get; set; }

    [Required]
    [MaxLength(100)]
    public string Name { get; set; }

    [Range(0, double.MaxValue)] 
    public double StockAmount { get; set; }

    [Range(0, double.MaxValue)] 
    public double MinStockAmount { get; set; }

    [Range(0, double.MaxValue)] 
    public double UnitPrice { get; set; }

    [Required]
    [MaxLength(20)] 
    public string UnitType { get; set; }

    [Required]
    public DateTime CreatedAtUtc { get; set; }

    public DateTime? UpdatedAtUtc { get; set; }
    public string? UserId { get; set; }

    [ForeignKey("UserId")]
    public Users User { get; set; }

    public int? CategoriesId { get; set; }

    [ForeignKey("CategoriesId")]
    public Categories Categories { get; set; }

    public ICollection<Transactions> Transactions { get; set; } = new List<Transactions>();
}

DbContext当前配置 #

builder.Entity<Products>()
.HasOne(p => p.User)
.WithMany(u => u.Products)
.HasForeignKey(p => p.UserId)
.OnDelete(DeleteBehavior.NoAction); 

builder.Entity<Products>()
    .HasOne(p => p.Categories)
    .WithMany(c => c.Products)
    .HasForeignKey(p => p.CategoriesId)
    .OnDelete(DeleteBehavior.Restrict); 

Transactions类 #

public class Transactions
{
    [Key]
    public int Id { get; set; }

    [Range(0, double.MaxValue)] 
    public double Qty { get; set; }

    [Required]
    [MaxLength(20)]
    public string? UnitType { get; set; }

    [Range(0, double.MaxValue)] 
    public double TotalPrice { get; set; }

    [Required]
    [MaxLength(20)] 
    public string? TransactionTypes { get; set; }

    [Required]
    public DateTime DateTime { get; set; }

    [Required]
    public string? UserId { get; set; }

    [ForeignKey("UserId")]
    public Users Users { get; set; }

    public string? ProductsId { get; set; }

    [ForeignKey("ProductsId")]
    public Products? Products { get; set; }
}

Users类 #

public class Users : IdentityUser, ITimeAuditable
{
    [Required]
    public DateTime CreatedAtUtc { get; set; }

    public DateTime? UpdatedAtUtc { get; set; }

    public ICollection<Products> Products { get; set; } = new List<Products>();
    public ICollection<Transactions> Transactions { get; set; } = new List<Transactions>();
}

Categories类 #

public class Categories:ITimeAuditable
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string Name { get; set; }
    public DateTime CreatedAtUtc { get; set; }
    public DateTime? UpdatedAtUtc { get; set; }

    public ICollection<Products> Products { get; set; }

    public string UsersId { get; set; }
    [ForeignKey("UsersId")]
    public Users Users { get; set; }
}

解决方案 #

需要显式配置所有关联的删除行为，消除多路径：

修改DbContext的配置，添加以下内容：

// 配置Categories与Users的关联，禁用级联删除
builder.Entity<Categories>()
    .HasOne(c => c.Users)
    .WithMany()
    .HasForeignKey(c => c.UsersId)
    .OnDelete(DeleteBehavior.Restrict);

// 配置Transactions与Users的关联，禁用级联删除
builder.Entity<Transactions>()
    .HasOne(t => t.Users)
    .WithMany(u => u.Transactions)
    .HasForeignKey(t => t.UserId)
    .OnDelete(DeleteBehavior.Restrict);

// 配置Transactions与Products的关联，禁用级联删除
builder.Entity<Transactions>()
    .HasOne(t => t.Products)
    .WithMany(p => p.Transactions)
    .HasForeignKey(t => t.ProductsId)
    .OnDelete(DeleteBehavior.Restrict);

同时保留你原有的Products配置不变。这样所有可能的级联路径都被限制，SQL Server就能成功创建约束。

如果需要级联删除的业务逻辑，不要依赖数据库级联，而是在代码中手动处理：比如删除用户前，先删除其关联的Transactions、Products、Categories，或根据业务需求调整删除顺序。

内容的提问来源于stack exchange，提问作者SAFIQUL ISLAM UZZAL 203-15-144