0

I work with Entity Framework (package 6.2.0) Code First and try to create a unique index on two fields which are both a foreign key. Instead of creating 1 unique index, it generates two separate indexes. Below my entities:

public class EntityA
{
    // PK identity
    public int Id { get; set; }
    // Using unique index as alternative to Alternate key
    [MaxLength(16)]
    [Index(IsUnique = true)] 
    public string Name { get; set; }
    public string Description { get; set; }
    public virtual ICollection<LinkedEntities> LinkedEntities { get; set; }
}

public class EntityB
{
    // PK identity
    public int Id { get; set; }
    // Using unique index as alternative to Alternate key
    [MaxLength(16)]
    [Index(IsUnique = true)]
    public string Name { get; set; }
    public string Description { get; set; }
    public virtual ICollection<LinkedEntities> LinkedEntities { get; set; }
}

public class LinkedEntities
{
    // PK identity
    public int Id { get; set; }
    // Using unique index on combination of A and B as alternative to Alternate key
    [Index("IX_UniqueLinkedEntities", IsUnique = true, Order = 1)]
    public EntityA EntityA { get; set; }
    [Index("IX_UniqueLinkedEntities", IsUnique = true, Order = 2)]
    public EntityB EntityB { get; set; }
}

And here the output of the migration scripts which gets generated for these entities:

    public override void Up()
    {
        CreateTable(
            "dbo.EntityA",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(maxLength: 16),
                    Description = c.String(),
                })
            .PrimaryKey(t => t.Id)
            .Index(t => t.Name, unique: true);

        CreateTable(
            "dbo.LinkedEntities",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    EntityA_Id = c.Int(),
                    EntityB_Id = c.Int(),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.EntityA", t => t.EntityA_Id)
            .ForeignKey("dbo.EntityB", t => t.EntityB_Id)
            .Index(t => t.EntityA_Id)
            .Index(t => t.EntityB_Id);

        CreateTable(
            "dbo.EntityB",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(maxLength: 16),
                    Description = c.String(),
                })
            .PrimaryKey(t => t.Id)
            .Index(t => t.Name, unique: true);
    }

Once the migration script is executed I see 3 indexes on my LinkedEntities table in the database:

  • IX_EntityA_Id (Non-Unique, Non-Clustered)
  • IX_EntityB_Id (Non-Unique, Non-Clustered)
  • PK_dbo.LinkedEntities (Clustered)

Why does EF not create the unique index "IX_UniqueLinkedEntities" on the combination of the two fields EntityA and EntityB? I get the feeling it has something to do with the fact both fields are also a FK.

Is the only way to create my unique constraint in this case to do something as suggested here? Or include raw SQL DDL in the migration script myself?

DeMaki
  • 371
  • 4
  • 15
  • 1
    Why not just make a composite key on your LinkedEntities object as in [this example](https://stackoverflow.com/questions/10866706/mapping-many-to-many-relationship-w-foreign-key-reference)? – Steve Greene Apr 26 '19 at 16:47
  • That is perfect example @SteveGreene! I decided to follow that approach and created a composite key and removed the identity column. – DeMaki Apr 29 '19 at 07:54

0 Answers0