mysql - 错误: Cannot add or update a child row: a foreign key constraint fails

Question

我仍在学习 EF Core，并且在尝试仅将用户数据(没有 BusinessUserProfile 和 BusinessCompany)插入数据库时​​遇到错误。我有以下表格:用户、BusinessUserProfile 和 BusinessCompany。其关系为User有一个BusinessUserProfile，BusinessCompany有多个BusinessUserProfile。

我的类(class)是这样创建的:

用户模型类:

public class User
{
    public int Id { get; set; }
    public string Username { get; set; }

    public BusinessUserProfile BusinessUserProfile { get; set; }

    public User()
    {
        BusinessUserProfile = new BusinessUserProfile();
    }
}

BusinessUserProfile 模型类:

public class BusinessUserProfile
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public BusinessCompany BusinessCompany { get; set; }
    public int BusinessCompanyId { get; set; }

    public User User { get; set; }
    public int UserId { get; set; }
}

}

BusinessCompany 模型类:

public class BusinessCompany
{
    public int Id { get; set; }
    public string Name { get; set; }

    public ICollection<BusinessUserProfile> BusinessUserProfiles { get; set; }

    public BusinessCompany()
    {
        BusinessUserProfiles = new Collection<BusinessUserProfile>();
    }
}

DataContext.cs:

modelBuilder.Entity<User>()
            .HasOne(u => u.BusinessUserProfile)
            .WithOne(bup => bup.User)
            .HasForeignKey<BusinessUserProfile>(bup => bup.UserId);

modelBuilder.Entity<BusinessCompany>()
            .HasMany(bc => bc.BusinessUserProfiles)
            .WithOne(bup => bup.BusinessCompany)
            .HasForeignKey(bup => bup.BusinessCompanyId);

当我将用户添加到数据库时，出现错误:MySqlException:无法添加或更新子行:外键约束失败(mydb.businessuserprofiles，CONSTRAINT FK_BusinessUserProfiles_BusinessCompanies_BusinessCompanyId FOREIGN KEY (BusinessCompanyId )引用businesscompanies(id)删除C)。

有人可以给我建议吗？

Answer 1

这种类型的错误通常是在子表的外键记录指向父记录(由于某种原因不再存在)时引起的。在您的特定情况下，这通常意味着 BusinessUserProfile 表中有一条或多条记录引用了 BusinessCompany 表中不再存在的记录。

您可以运行以下查询来标记一些孤立的子记录:

SELECT *
FROM BusinessUserProfile bup
WHERE NOT EXISTS (SELECT 1 FROM BusinessCompany bc
                  WHERE bc.id = bup.BusinessCompanyId);

如果此查询找到记录，则它解释了您的观察结果。如果不是，那么至少可以排除这种可能性。