如何解决Microsoft.EntityFrameworkCore.DbUpdateException外键约束删除冲突

实体类定义 #

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

    public int DoctorId { get; set; }
    public Doctor Doctor { get; set; }

    public int? InstitutionId { get; set; }
    public Institution? Institution { get; set; }
}

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

    public string? Name { get; set; }

    public HeadDoctor HeadDoctor { get; set; }
} 

DbContext配置 #

builder.Entity<Institution>()
        .HasOne(institution => institution.HeadDoctor)
        .WithOne(headDoctor => headDoctor.Institution)
        .HasForeignKey<HeadDoctor>(headDoctor => headDoctor.InstitutionId);

错误信息 #

SqlException: DELETE 语句与 REFERENCE 约束"FK_HeadDoctor_Institution_InstitutionId"冲突。冲突发生在数据库"MedicDb"，表"dbo.HeadDoctor"，列'InstitutionId'。

修复方案 #

该错误的原因是删除Institution时，关联的HeadDoctor记录仍持有指向该机构的外键值，违反了数据库的外键约束。可通过以下三种方式解决：

• 配置级联删除
  在一对一关系配置中添加级联删除规则，删除机构时自动删除对应的院长记录：

  builder.Entity<Institution>()
          .HasOne(institution => institution.HeadDoctor)
          .WithOne(headDoctor => headDoctor.Institution)
          .HasForeignKey<HeadDoctor>(headDoctor => headDoctor.InstitutionId)
          .OnDelete(DeleteBehavior.Cascade);
  

• 删除前清空关联外键
  若需保留HeadDoctor记录，可在删除机构前找到关联的院长，将其InstitutionId设为null并保存修改：

  var institution = await _context.Institutions.Include(i => i.HeadDoctor).FirstOrDefaultAsync(i => i.Id == targetId);
  if (institution != null)
  {
      if (institution.HeadDoctor != null)
      {
          institution.HeadDoctor.InstitutionId = null;
          _context.Update(institution.HeadDoctor);
      }
      _context.Institutions.Remove(institution);
      await _context.SaveChangesAsync();
  }
  

• 配置删除时设置外键为Null
  利用已有的可空外键，配置删除机构时自动将关联院长的InstitutionId设为null：

  builder.Entity<Institution>()
          .HasOne(institution => institution.HeadDoctor)
          .WithOne(headDoctor => headDoctor.Institution)
          .HasForeignKey<HeadDoctor>(headDoctor => headDoctor.InstitutionId)
          .OnDelete(DeleteBehavior.SetNull);
  

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