2

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:

enter image description here

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

Best Regards Kajac

Erik Philips
  • 53,428
  • 11
  • 128
  • 150
kajac
  • 128
  • 1
  • 5
  • Well, I don't really understand your entities and what they are supposed to do. But it sounds like instead of having 2 FKs on `Competitor`, you should remove them from there, and instead add a foreign key on both `Equestrian` and `TeamEquestrian` pointing back to `Competitor`. – sstan Jun 29 '15 at 14:41

2 Answers2

2

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.

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
0

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)

Erik Philips
  • 53,428
  • 11
  • 128
  • 150
  • This still allows two different Equestrians to refer to the same competitor. – Gert Arnold Jun 29 '15 at 20:52
  • Agreed, however that was not a criteria the OP asked for. This is the only solution without dramatically changing the DB structure and is very common practice. – Erik Philips Jun 29 '15 at 23:33
  • Cool with me, but the requirement "should have a relation with one of two tables, but not both" is not enforced this way. It's a business rule violation waiting to happen. – Gert Arnold Jun 30 '15 at 23:02