Is it possible to have a relation where the foreig

Question

I have a table in a SQL database which should have a relation with one of two tables, but not both.

My manager solved it by using the ID field of the table Competitor as both primary key and as foreign key for the two other tables. None of the tables have autoincremental IDs.

Problem is, when I try to add a new compeitor, it fails unless there is an entry in both of the other tables with the specified ID, which is the opposite of what I want.

Here is an illustration to show you how it's done:

I am sorry if this has been posted or answered before. I could not find anything while searching.

Best Regards Kajac

Answer 1

The only way to get this right is by subtyping the two Equestrian classes:

public class EquestrianBase
{
    public int Id { get; set; }
    public virtual Competitor Competitor { get; set; }
}
public class Equestrian : EquestrianBase
{
    // Other properties
}

public class TeamEquestrian : EquestrianBase
{
    public int TeamLeaderPersonId { get; set; }
    // Other properties
}

This could be the competitor class:

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

    public virtual EquestrianBase EquestrianBase { get; set; }

    // Other properties
}

And the essential configuration:

modelBuilder.Entity<EquestrianBase>()
            .HasRequired(e => e.Competitor)
            .WithOptional();

Using this model, you'll see that EF adds a Discriminator column to the Equestrian table (of which there is only one now) which discriminates between the two types. Now the database schema enforces that a Competitor have only one Equestrian of either type.

If you want to further fine-tune the inheritance schema, which is called table per hierarchy, look here.

Answer 2

Sure just set the key as Foreign and Primary on the "dependant properties". The Competitor should have the primary key.

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

public class Equestrain
{
  [Key]
  [ForeignKey("Competitor")]
  public int Id{ get; set; }

  public Competitor Competitor { get; set; }
}

public class TeamEquestrain
{
  [Key]
  [ForeignKey("Competitor")]
  public int Id{ get; set; }

  public Competitor Competitor { get; set; }
}

MSDN - Configuring a Required to Optional Relationship (One to Zero or One)