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?