EF Adding Alternate Key when Unique Clustered Index already Exists
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
### Issue Summary
According to the [Microsoft Documents](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.metadata.builders.referencereferencebuilder-2.hasprincipalkey?view=efcore-6.0) for `HasPrincipalKey` a new unique constraint should only be introduced if properties do not already have a unique constraint in place.
> Configures the unique property(s) that this relationship targets. Typically you would only call this method if you want to use a property(s) other than the primary key as the principal property(s). If the specified property(s) is not already a unique constraint (or the primary key) then a new unique constraint will be introduced.
However, if I already have a unique clustered index in place on those columns, an additional key is being added in the migration definition. This is a duplicate constraint as the Unique Clustered Index should be enough to handle this use-case and the AlternateKey (`AK_Entitiy1s_TenantId_Entity1Id`) should not be added
### DB Context Definition
```C#
public class Tenant
{
public Guid TenantId { get; set; }
public string Name { get; set; }
public IList Entity1s { get; set; }
public IList Entity2s { get; set; }
}
public class Entity1
{
public Guid Entity1Id { get; set; }
public Guid TenantId { get; set; }
public Tenant Tenant { get; set; }
public IList Entity2s { get; set; }
}
public class Entity2
{
public Guid Entity2Id { get; set; }
public Guid Entity1Id { get; set; }
public Guid TenantId { get; set; }
public Tenant Tenant { get; set; }
public Entity1 Entity1 { get; set; }
}
public class Context : DbContext
{
public DbSet Tenants { get; set; }
public DbSet Entitiy1s { get; set; }
public DbSet Entitiy2s { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("myConnectionString");
base.OnConfiguring(optionsBuilder);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity()
.HasKey(x => x.TenantId)
.IsClustered();
modelBuilder.Entity()
.HasKey(x => x.Entity1Id)
.IsClustered(false);
modelBuilder.Entity()
.HasIndex(x=> new {x.TenantId, x.Entity1Id})
.IsUnique()
.IsClustered();
modelBuilder.Entity()
.HasKey(x => x.Entity2Id)
.IsClustered(false);
modelBuilder.Entity()
.HasIndex(x => new { x.TenantId, x.Entity2Id })
.IsUnique()
.IsClustered();
modelBuilder.Entity()
.HasOne(x => x.Tenant)
.WithMany(x => x.Entity1s);
modelBuilder.Entity()
.HasOne(x => x.Tenant)
.WithMany(x => x.Entity2s);
modelBuilder.Entity()
.HasOne(x => x.Entity1)
.WithMany(x => x.Entity2s)
.HasForeignKey(x=> new {x.TenantId, x.Entity1Id})
.HasPrincipalKey(x=> new {x.TenantId, x.Entity1Id});
}
}
```
### Migration Code
See Comment in code for where I have the issue
```C#
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Tenants",
columns: table => new
{
TenantId = table.Column(type: "uniqueidentifier", nullable: false),
Name = table.Column(type: "nvarchar(max)", nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Tenants", x => x.TenantId)
.Annotation("SqlServer:Clustered", true);
});
migrationBuilder.CreateTable(
name: "Entitiy1s",
columns: table => new
{
Entity1Id = table.Column(type: "uniqueidentifier", nullable: false),
TenantId = table.Column(type: "uniqueidentifier", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Entitiy1s", x => x.Entity1Id)
.Annotation("SqlServer:Clustered", false);
table.UniqueConstraint("AK_Entitiy1s_TenantId_Entity1Id", x => new { x.TenantId, x.Entity1Id }); // Alternate Key being Added even though we have a Unique Clustered Index already defined. This is a duplicate constraint
table.ForeignKey(
name: "FK_Entitiy1s_Tenants_TenantId",
column: x => x.TenantId,
principalTable: "Tenants",
principalColumn: "TenantId",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateTable(
name: "Entitiy2s",
columns: table => new
{
Entity2Id = table.Column(type: "uniqueidentifier", nullable: false),
Entity1Id = table.Column(type: "uniqueidentifier", nullable: false),
TenantId = table.Column(type: "uniqueidentifier", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Entitiy2s", x => x.Entity2Id)
.Annotation("SqlServer:Clustered", false);
table.ForeignKey(
name: "FK_Entitiy2s_Entitiy1s_TenantId_Entity1Id",
columns: x => new { x.TenantId, x.Entity1Id },
principalTable: "Entitiy1s",
principalColumns: new[] { "TenantId", "Entity1Id" },
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_Entitiy2s_Tenants_TenantId",
column: x => x.TenantId,
principalTable: "Tenants",
principalColumn: "TenantId",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateIndex(
name: "IX_Entitiy1s_TenantId_Entity1Id",
table: "Entitiy1s",
columns: new[] { "TenantId", "Entity1Id" },
unique: true)
.Annotation("SqlServer:Clustered", true);
migrationBuilder.CreateIndex(
name: "IX_Entitiy2s_TenantId_Entity1Id",
table: "Entitiy2s",
columns: new[] { "TenantId", "Entity1Id" });
migrationBuilder.CreateIndex(
name: "IX_Entitiy2s_TenantId_Entity2Id",
table: "Entitiy2s",
columns: new[] { "TenantId", "Entity2Id" },
unique: true)
.Annotation("SqlServer:Clustered", true);
}
```
### Include provider and version information
EF Core version: 6.4.4
EF Core Design Version: 6.0.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6
Operating system: Windows 10
IDE: Visual Studio 2022 17.1.4
Contributor guide
Assessment
This issue has not been assessed yet.