多个外键到同一个主键表 [英] Multiple foreign keys to same primary key table

问题描述

我有一个表，其中包含多个字段，这些字段是另一个表中主键的外键。例如：

 固定ID（PK）
 HomeTeamId（从FK到Team.TeamId）
 AwayTeamId（ FK到Team.TeamId）
 HomeTeamCoachId（FK到Coach.CoachId）
 AwayTeamCoachId（FK到Coach.CoachId）
  

最好将这些数据分为两个表，其中包含FixtureId的外键吗？当前是由实体框架生成的：

  FixtureId PK 
 HomeTeamId int 
 AwayTeamId int 
 HomeTeamCoachId int 
 AwayTeamCoachId int 
 AwayTeam_TeamId FK 
 HomeTeam_TeamId FK 
 AwayTeamCoach_CoachId FK 
 HomeTeamCoach_CoachId FK 
  

这是通过此类生成的：

 公共局部类夹具
 {
 public int FixtureId {组; } 
 
 //外键
 public int AwayTeamId {get;组; } 
 //导航属性
公共虚拟Team AwayTeam {get;组; } 
 
 //外键
 public int HomeTeamId {get;组; } 
 //导航属性
公共虚拟团队HomeTeam {get;组; } 
 
 //外键
 public int AwayCoachId {get;组; } 
 //导航属性
公共虚拟教练AwayCoach {get;组; } 
 
 //外键
 public int HomeCoachId {get;组; } 
 //导航属性
 public virtual Coach HomeCoach {get;组; } 
} 
  

有人可以告诉我这样做是否正确吗？ / p>

编辑：回复Slauma

所以我的课程基本上是这样的？还是OnModelCreating中的配置意味着我在Fixture类中不需要一些与外键相关的代码？

 受保护的覆盖void OnModelCreating（DbModelBuilder modelBuilder）
 {
 //实体类型配置
 modelBuilder.Configurations.Add（new TeamConfiguration（））; 
 modelBuilder.Configurations.Add（new CoachConfiguration（））; 
 modelBuilder.Configurations.Add（new FixtureConfiguration（））; 
 
 modelBuilder.Entity< Fixture>（）
 .HasRequired（f => f.AwayTeam）
 .WithMany（）
 .HasForeignKey（f => f.AwayTeamId）
 .WillCascadeOnDelete（false）; 
 
 modelBuilder.Entity< Fixture>（）
 .HasRequired（f => f.HomeTeam）
 .WithMany（）
 .HasForeignKey（f => f.HomeTeamId）
 .WillCascadeOnDelete（false）; 
 
 modelBuilder.Entity< Fixture>（）
 .HasRequired（f => f.AwayCoach）
 .WithMany（）
 .HasForeignKey（f => f.AwayCoachId）
 .WillCascadeOnDelete（false）; 
 
 modelBuilder.Entity< Fixture>（）
 .HasRequired（f => f.HomeCoach）
 .WithMany（）
 .HasForeignKey（f => f.HomeCoachId）
 .WillCascadeOnDelete（false）; 
} 
 
公共局部类治具
 {
 public int FixtureId {get;组; } 
公用字串Season {get;组; } 
个公共字节Week {get;组; } 
 
 //外键
 public int AwayTeamId {get;组; } 
 //导航属性
公共虚拟Team AwayTeam {get;组; } 
 
 //外键
 public int HomeTeamId {get;组; } 
 //导航属性
公共虚拟团队HomeTeam {get;组; } 
 
 //外键
 public int AwayCoachId {get;组; } 
 //导航属性
公共虚拟教练AwayCoach {get;组; } 
 
 //外键
 public int HomeCoachId {get;组; } 
 //导航属性
 public virtual Coach HomeCoach {get;组; } 
 
个公共字节AwayTeamScore {get;组; } 
公用字节HomeTeamScore {get;组; } 
} 
  

解决方案

显然，EF不检测您的 int 属性（例如 AwayTeamId ）作为导航属性（例如 AwayTeam ）的外键，因为 Team 中的主键属性不是 Id 而是 TeamId 。如果将FK命名为 AwayTeamTeamId ，或者 Team 中的主键属性的名称为 Id 。

如果您不想根据EF约定更改这些属性名称，则可以使用以下命令定义FK：数据注释：

  [ForeignKey（ AwayTeam）] 
 public int AwayTeamId {get;组; } 
个公共虚拟团队AwayTeam {get;组; } 
 
 //其他三个FK相同
  

或Fluent API ：

  modelBuilder.Entity< Fixture>（）
 .HasRequired（f => f.AwayTeam）
 .WithMany（）
 .HasForeignKey（f => f.AwayTeamId）
 .WillCascadeOnDelete（false）; 
 
 //其他三个FK相同
  

我禁用了级联删除，因为默认情况下将为必需的关系启用它。但是，由于您与 Team 表（以及 Coach 表）具有两个必需的关系，因此会导致两个级联删除路径，从 Fixture 到 Team 和 Coach 。 SQL Server中禁止使用多个级联删除路径，因此您必须对 Fixture 和 Team 之间的两种关系中的至少一种禁用级联删除。 c $ c>（且在固定设备和教练之间）。

I have a table with multiple fields that are foreign keys to a primary key in another table. For example :

Fixture Id (PK)
HomeTeamId (FK to Team.TeamId)
AwayTeamId (FK to Team.TeamId)
HomeTeamCoachId (FK to Coach.CoachId)
AwayTeamCoachId (FK to Coach.CoachId)

Would it be better to separate this data into 2 tables HomeTeam and AwayTeam with a foreign key to FixtureId? This is currently what was generated by Entity Framework :

FixtureId PK
HomeTeamId int
AwayTeamId int
HomeTeamCoachId int
AwayTeamCoachId int
AwayTeam_TeamId FK
HomeTeam_TeamId FK
AwayTeamCoach_CoachId FK
HomeTeamCoach_CoachId FK

This was generated through this class :

public partial class Fixture
{
    public int FixtureId { get; set; }

    //foreign key
    public int AwayTeamId { get; set; }
    //navigation properties
    public virtual Team AwayTeam { get; set; }

    //foreign key
    public int HomeTeamId { get; set; }
    //navigation properties
    public virtual Team HomeTeam { get; set; }

    //foreign key
    public int AwayCoachId { get; set; }
    //navigation properties
    public virtual Coach AwayCoach { get; set; }

    //foreign key
    public int HomeCoachId { get; set; }
    //navigation properties
    public virtual Coach HomeCoach { get; set; }
}

Can anybody tell me if this is the correct way to do this?

EDIT : In reply to Slauma

So my classes would basically look like this? Or does the configuration in OnModelCreating mean I don't need some of the foreign key related code in my Fixture class?

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Entity Type Configuration
        modelBuilder.Configurations.Add(new TeamConfiguration());
        modelBuilder.Configurations.Add(new CoachConfiguration());
        modelBuilder.Configurations.Add(new FixtureConfiguration());

        modelBuilder.Entity<Fixture>()
            .HasRequired(f => f.AwayTeam)
            .WithMany()
            .HasForeignKey(f => f.AwayTeamId)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<Fixture>()
            .HasRequired(f => f.HomeTeam)
            .WithMany()
            .HasForeignKey(f => f.HomeTeamId)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<Fixture>()
            .HasRequired(f => f.AwayCoach)
            .WithMany()
            .HasForeignKey(f => f.AwayCoachId)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<Fixture>()
            .HasRequired(f => f.HomeCoach)
            .WithMany()
            .HasForeignKey(f => f.HomeCoachId)
            .WillCascadeOnDelete(false);
    }

public partial class Fixture
{
    public int FixtureId { get; set; }
    public string Season { get; set; }
    public byte Week { get; set; }

    //foreign key
    public int AwayTeamId { get; set; }
    //navigation properties
    public virtual Team AwayTeam { get; set; }

    //foreign key
    public int HomeTeamId { get; set; }
    //navigation properties
    public virtual Team HomeTeam { get; set; }

    //foreign key
    public int AwayCoachId { get; set; }
    //navigation properties
    public virtual Coach AwayCoach { get; set; }

    //foreign key
    public int HomeCoachId { get; set; }
    //navigation properties
    public virtual Coach HomeCoach { get; set; }

    public byte AwayTeamScore { get; set; }
    public byte HomeTeamScore { get; set; }
}

解决方案

Apparently EF doesn't detect your int properties like AwayTeamId as the foreign key for the navigation properties like AwayTeam because the primary key property in Team is not Id but TeamId. It would probably detect the FKs if they are named like AwayTeamTeamId or if the primary key property in Team has the name Id.

If you don't want to change those property names according to EF convention you can define the FKs with data annotations:

[ForeignKey("AwayTeam")]
public int AwayTeamId { get; set; }
public virtual Team AwayTeam { get; set; }

// the same for the other three FKs

Or Fluent API:

modelBuilder.Entity<Fixture>()
    .HasRequired(f => f.AwayTeam)
    .WithMany()
    .HasForeignKey(f => f.AwayTeamId)
    .WillCascadeOnDelete(false);

// the same for the other three FKs

I have disabled cascading delete because it will be enabled by default for a required relationship. But because you have two required relationships to the Team table (and the Coach table as well) it would result in two cascading delete paths from Fixture to Team and Coach. Multiple cascading delete paths are forbidden in SQL Server, so you must disable cascading delete for at least one of the two relationships between Fixture and Team (and between Fixture and Coach).

这篇关于多个外键到同一个主键表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！