DeleteBehavior.SetNull在Sqlite正常但Sql Server失效的技术咨询

一、问题根源：Sql Server的限制仍然存在 #

是的，截至2023年，Sql Server仍然禁止自引用外键设置ON DELETE SET NULL（或其他级联操作）。这是因为Sql Server的约束检查机制会将自引用级联操作判定为潜在的循环级联路径，哪怕你的业务逻辑中不会出现实际循环，也会直接阻止这种外键约束的创建，这和Sqlite的宽松处理逻辑完全不同。

你遇到的两个错误正是这个限制的直接体现：

Microsoft.Data.SqlClient.SqlException (0x80131904): DELETE语句与同表引用约束"FK_Partners_Partners_ParentId"冲突。冲突发生在数据库"master"的dbo.Partners表的ParentId列。

在表'Partners'上引入外键约束'FK_Partners_Partners_ParentId'可能会导致循环或多个级联路径。请指定ON DELETE NO ACTION或ON UPDATE NO ACTION，或修改其他外键约束。无法创建约束或索引。请查看先前的错误。

二、通用解决方法 #

因为数据库层面不支持，需要在EF Core代码层面或通过数据库触发器来实现需求：

1. EF Core手动处理级联更新（推荐，适用于所有表） #

在删除父实体前，主动查询所有关联的子实体，将它们的外键字段设为null，保存更改后再删除父实体。

针对Partner表的示例代码：

var parentPartner = await dbContext.Partners.FindAsync(parentId);
if (parentPartner != null)
{
    // 查询所有关联的子Partner
    var childPartners = await dbContext.Partners
        .Where(p => p.ParentId == parentId)
        .ToListAsync();
    
    // 将子Partner的ParentId设为null
    foreach (var child in childPartners)
    {
        child.ParentId = null;
    }
    
    // 删除父Partner并保存更改
    dbContext.Partners.Remove(parentPartner);
    await dbContext.SaveChangesAsync();
}

通用封装方法（适用于所有带外键的表）

可以封装一个通用方法，通过EF Core的元数据自动处理所有关联实体的外键置空：

public async Task DeleteWithSetNullAsync<T>(DbContext context, T entity) where T : class
{
    var entityType = context.Model.FindEntityType(typeof(T));
    var primaryKey = entityType.FindPrimaryKey().Properties.First();
    var entityId = primaryKey.Getter.GetValue(entity);

    // 获取所有指向当前实体的外键（包括自引用）
    var referencingForeignKeys = entityType.GetReferencingForeignKeys();
    foreach (var fk in referencingForeignKeys)
    {
        var childEntityType = fk.DeclaringEntityType;
        var fkProperty = fk.Properties.First();

        // 查询所有关联的子实体
        var childEntities = await context.Set(childEntityType.ClrType)
            .Where(e => EF.Property<object>(e, fkProperty.Name) == entityId)
            .ToListAsync();

        // 将子实体的外键设为null
        foreach (var child in childEntities)
        {
            fkProperty.Setter.SetValue(child, null);
        }
    }

    context.Remove(entity);
    await context.SaveChangesAsync();
}

2. Sql Server触发器（数据库层面处理） #

为每个有自引用外键的表创建DELETE触发器，在删除父记录时自动将子记录的外键设为null。

针对Partner表的触发器示例：

CREATE TRIGGER trg_Partners_Delete_SetNullParent
ON dbo.Partners
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    -- 将所有关联子记录的ParentId设为null
    UPDATE dbo.Partners
    SET ParentId = NULL
    WHERE ParentId IN (SELECT Id FROM DELETED);
END

注意：这种方法需要为每个涉及的表单独创建触发器，灵活性不如EF Core的通用代码。

3. 调整EF Core配置配合手动处理 #

先将自引用外键的DeleteBehavior设为Restrict（避免迁移报错），再用第一种方法的逻辑手动处理删除：

模型配置示例：

modelBuilder.Entity<Partner>()
    .HasOne(p => p.Parent)
    .WithMany() // 如果有子集合属性，可改为.WithMany(p => p.Children)
    .HasForeignKey(p => p.ParentId)
    .OnDelete(DeleteBehavior.Restrict);

三、为什么Sqlite可以正常工作？ #

Sqlite的外键约束检查逻辑更宽松：它采用延迟约束检查，并且对自引用的级联操作没有严格限制，允许ON DELETE SET NULL这类操作直接生效，这是两种数据库的设计差异导致的。

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