I've been working recently on setting up a code first database via EF Core 7.0.4 but I'm encountering a strange behavior when I run the migrations -> extra alternate keys and indexes are being generated and I don't get it. Let me walk you through it:
I've first worked on a SQL script for designing and architecting the database structure - tables, constraints and so on. Here's a mock of my SQL script:
CREATE TABLE [Product].[Family]
(
uid uniqueidentifier
CONSTRAINT [DF_Product_Family_UID] DEFAULT (NEWSEQUENTIALID()) ROWGUIDCOL NOT NULL,
code int IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL,
description nvarchar(50) NOT NULL,
created datetime
CONSTRAINT [DF_Product_Family_Created] DEFAULT GETDATE() NOT NULL,
updated datetime
CONSTRAINT [DF_Product_Family_Updated] DEFAULT GETDATE() NOT NULL,
inactive bit
CONSTRAINT [DF_Product_Family_Inactive] DEFAULT (0) NOT NULL,
CONSTRAINT [PK_Product_Family_UID] PRIMARY KEY NONCLUSTERED (uid ASC)
)
CREATE UNIQUE CLUSTERED INDEX [IX_Product_Family_Code]
ON [Product].[Family] (code ASC)
GO
CREATE TABLE [Product].[Category]
(
uid uniqueidentifier
CONSTRAINT [DF_Product_Category_UID] DEFAULT (NEWSEQUENTIALID()) ROWGUIDCOL NOT NULL,
code int IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL,
description nvarchar(50) NOT NULL,
fFamilyCode int NOT NULL,
created datetime
CONSTRAINT [DF_Product_Category_Created] DEFAULT GETDATE() NOT NULL,
updated datetime
CONSTRAINT [DF_Product_Category_Updated] DEFAULT GETDATE() NOT NULL,
inactive bit
CONSTRAINT [DF_Product_Category_Inactive] DEFAULT (0) NOT NULL,
CONSTRAINT [PK_Product_Category_UID] PRIMARY KEY NONCLUSTERED (uid ASC)
)
CREATE UNIQUE CLUSTERED INDEX [IX_Product_Category_Code]
ON [Product].[Category] (code ASC)
GO
ALTER TABLE [Product].[Category]
ADD CONSTRAINT [FK_Category_Product_Family]
FOREIGN KEY (fFamilyCode) REFERENCES [Product].[Family] (code)
Then, I replicated these SQL entities into models in the project solution:
public partial class Family
{
[Column(Order = 0)]
public Guid Uid { get; set; }
[Column(Order = 1)]
public int Code { get; set; }
[Column(Order = 2)]
public string Description { get; set; } = null!;
[Column(Order = 3)]
public DateTime Created { get; set; }
[Column(Order = 4)]
public DateTime Updated { get; set; }
[Column(Order = 5)]
public bool Inactive { get; set; }
public virtual ICollection<Category> Categories { get; } = new List<Category>();
public virtual ICollection<Product> Products { get; } = new List<Product>();
public virtual ICollection<SubCategory> SubCategories { get; } = new List<SubCategory>();
}
public partial class Category
{
[Column(Order = 0)]
public Guid Uid { get; set; }
[Column(Order = 1)]
public int Code { get; set; }
[Column(Order = 2)]
public string Description { get; set; } = null!;
[Column(Order = 3)]
public int FFamilyCode { get; set; }
[Column(Order = 4)]
public DateTime Created { get; set; }
[Column(Order = 5)]
public DateTime Updated { get; set; }
[Column(Order = 6)]
public bool Inactive { get; set; }
public virtual Family FFamilyCodeNavigation { get; set; } = null!;
public virtual ICollection<SubCategory> SubCategories { get; } = new List<SubCategory>();
public virtual ICollection<Product> Products { get; } = new List<Product>();
}
And finally, the corresponding database sets:
modelBuilder.Entity<Family>(entity =>
{
entity.ToTable("Family", "Product");
entity.HasKey(e => e.Uid)
.HasName("PK_Product_Family_UID")
.IsClustered(false);
entity.HasIndex(e => e.Code, "IX_Product_Family_Code")
.IsClustered();
entity.Property(e => e.Uid)
.HasDefaultValueSql("(newsequentialid())")
.HasColumnName("uid");
entity.Property(e => e.Code)
.UseIdentityColumn(1, 1)
.HasColumnName("code");
entity.Property(e => e.Description)
.HasMaxLength(50)
.HasColumnName("description");
entity.Property(e => e.Created)
.HasDefaultValueSql("(getdate())")
.HasColumnType("datetime")
.HasColumnName("created");
entity.Property(e => e.Updated)
.HasDefaultValueSql("(getdate())")
.HasColumnType("datetime")
.HasColumnName("updated");
entity.Property(e => e.Inactive)
.HasDefaultValue(false)
.HasColumnName("inactive");
});
modelBuilder.Entity<Category>(entity =>
{
entity.ToTable("Category", "Product");
entity.HasKey(e => e.Uid)
.HasName("PK_Product_Category_UID")
.IsClustered(false);
entity.HasIndex(e => e.Code, "IX_Product_Category_Code")
.IsUnique()
.IsClustered();
entity.Property(e => e.Uid)
.HasDefaultValueSql("(newsequentialid())")
.HasColumnName("uid");
entity.Property(e => e.Code)
.UseHiLo()
.UseIdentityColumn(1, 1)
.HasColumnName("code");
entity.Property(e => e.Description)
.HasMaxLength(50)
.HasColumnName("description");
entity.Property(e => e.FFamilyCode)
.HasColumnName("fFamilyCode");
entity.Property(e => e.Created)
.HasDefaultValueSql("(getdate())")
.HasColumnType("datetime")
.HasColumnName("created");
entity.Property(e => e.Updated)
.HasDefaultValueSql("(getdate())")
.HasColumnType("datetime")
.HasColumnName("updated");
entity.Property(e => e.Inactive)
.HasDefaultValue(false)
.HasColumnName("inactive");
entity.HasOne(d => d.FFamilyCodeNavigation).WithMany(p => p.Categories)
.HasForeignKey(d => d.FFamilyCode)
.HasPrincipalKey(d => d.Code)
.OnDelete(DeleteBehavior.Restrict)
.HasConstraintName("FK_Category_Product_Family");
});
Now, when I run the migrations for the [Product].[Family]
table (and not only), another AK and an extra index is being generated. In addition, when it comes to [Product].[Category]
table, an extra alternate key and since it is referencing the [Product].[Family]
table, it creates two more indexes, as shown in this screenshot:
Since I couldn't tell immediately why is this happening, thought to see the actual SQL script that EF Core generates and runs against my server. And here is it:
CREATE TABLE [Product].[Family]
(
[uid] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
[code] int NOT NULL IDENTITY,
[description] nvarchar(50) NOT NULL,
[created] datetime NOT NULL DEFAULT ((getdate())),
[updated] datetime NOT NULL DEFAULT ((getdate())),
[inactive] bit NOT NULL DEFAULT CAST(0 AS bit),
CONSTRAINT [PK_Product_Family_UID] PRIMARY KEY NONCLUSTERED ([uid]),
CONSTRAINT [AK_Family_code] UNIQUE ([code])
);
GO
CREATE TABLE [Product].[Category]
(
[uid] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
[code] int NOT NULL IDENTITY,
[description] nvarchar(50) NOT NULL,
[fFamilyCode] int NOT NULL,
[created] datetime NOT NULL DEFAULT ((getdate())),
[updated] datetime NOT NULL DEFAULT ((getdate())),
[inactive] bit NOT NULL DEFAULT CAST(0 AS bit),
CONSTRAINT [PK_Product_Category_UID] PRIMARY KEY NONCLUSTERED ([uid]),
CONSTRAINT [AK_Category_code] UNIQUE ([code]),
CONSTRAINT [FK_Category_Product_Family] FOREIGN KEY ([fFamilyCode]) REFERENCES [Product].[Family] ([code]) ON DELETE NO ACTION
);
GO
CREATE INDEX [IX_Category_fFamilyCode] ON [Product].[Category] ([fFamilyCode]);
GO
CREATE UNIQUE CLUSTERED INDEX [IX_Product_Category_Code] ON [Product].[Category] ([code]);
GO
Now, I have the answer about the extra indexes that are being created - because of the UNIQUE CONSTRAINT
but I really don't understand why EF Core does this and also, why it creates extra alternate keys? Here's how the two tables would/should look like if I just run the SQL script created by myself and not the one that EF Core uses:
Can anyone help me understand and fix it?
Please allow me to break your answer:
- EF Core supports only FKs referencing key (primary or alternate) in the principal entity. This is different from relational database which only require unique key in the referenced table.
I may be crazy or naive, but wasn't supposed to fall under the relational database rules?
- EF Core alternate key has the same requirements as primary key - non null, not mutable after insert and backed with unique constraint/index. The only difference from primary key is that alternate keys by default imply non clustered index.
True.
- Whenever you use HasPrincipalKey API, if the referenced field is not primary or alternate key, EF will create alternate key for you, with default conventional constraint/index names.
Yeah, I could see that but that's fine, I don't mind about the key itself.
So, as per your recommendation to change the property from .HasIndex()
to .HasAlternateKey()
, I've already tried it before. The database sets now look like this:
Family
modelBuilder.Entity<Family>(entity =>
{
entity.ToTable("Family", "Product");
entity.HasKey(e => e.Uid)
.HasName("PK_Product_Family_UID")
.IsClustered(false);
entity.HasAlternateKey(e => e.Code)
.HasName("IX_Product_Family_Code")
.IsClustered();
entity.Property(e => e.Uid)
.HasDefaultValueSql("(newsequentialid())")
.HasColumnName("uid");
entity.Property(e => e.Code)
.UseIdentityColumn(1, 1)
.HasColumnName("code");
entity.Property(e => e.Description)
.HasMaxLength(50)
.HasColumnName("description");
entity.Property(e => e.Created)
.HasDefaultValueSql("(getdate())")
.HasColumnType("datetime")
.HasColumnName("created");
entity.Property(e => e.Updated)
.HasDefaultValueSql("(getdate())")
.HasColumnType("datetime")
.HasColumnName("updated");
entity.Property(e => e.Inactive)
.HasDefaultValue(false)
.HasColumnName("inactive");
});
Category
modelBuilder.Entity<Category>(entity =>
{
entity.ToTable("Category", "Product");
entity.HasKey(e => e.Uid)
.HasName("PK_Product_Category_UID")
.IsClustered(false);
entity.HasAlternateKey(e => e.Code)
.HasName("IX_Product_Category_Code")
.IsClustered();
entity.Property(e => e.Uid)
.HasDefaultValueSql("(newsequentialid())")
.HasColumnName("uid");
entity.Property(e => e.Code)
.UseIdentityColumn(1, 1)
.HasColumnName("code");
entity.Property(e => e.Description)
.HasMaxLength(50)
.HasColumnName("description");
entity.Property(e => e.FFamilyCode)
.HasColumnName("fFamilyCode");
entity.Property(e => e.Created)
.HasDefaultValueSql("(getdate())")
.HasColumnType("datetime")
.HasColumnName("created");
entity.Property(e => e.Updated)
.HasDefaultValueSql("(getdate())")
.HasColumnType("datetime")
.HasColumnName("updated");
entity.Property(e => e.Inactive)
.HasDefaultValue(false)
.HasColumnName("inactive");
entity.HasOne(d => d.FFamilyCodeNavigation).WithMany(p => p.Categories)
.HasForeignKey(d => d.FFamilyCode)
.HasPrincipalKey(d => d.Code)
.OnDelete(DeleteBehavior.Restrict)
.HasConstraintName("FK_Category_Product_Family");
});
The generated migration
migrationBuilder.CreateTable(
name: "Family",
schema: "Product",
columns: table => new
{
uid = table.Column<Guid>(type: "uniqueidentifier", nullable: false, defaultValueSql: "(newsequentialid())"),
code = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
description = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false),
created = table.Column<DateTime>(type: "datetime", nullable: false, defaultValueSql: "(getdate())"),
updated = table.Column<DateTime>(type: "datetime", nullable: false, defaultValueSql: "(getdate())"),
inactive = table.Column<bool>(type: "bit", nullable: false, defaultValue: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Product_Family_UID", x => x.uid)
.Annotation("SqlServer:Clustered", false);
table.UniqueConstraint("IX_Product_Family_Code", x => x.code)
.Annotation("SqlServer:Clustered", true);
});
migrationBuilder.CreateTable(
name: "Category",
schema: "Product",
columns: table => new
{
uid = table.Column<Guid>(type: "uniqueidentifier", nullable: false, defaultValueSql: "(newsequentialid())"),
code = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
description = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false),
fFamilyCode = table.Column<int>(type: "int", nullable: false),
created = table.Column<DateTime>(type: "datetime", nullable: false, defaultValueSql: "(getdate())"),
updated = table.Column<DateTime>(type: "datetime", nullable: false, defaultValueSql: "(getdate())"),
inactive = table.Column<bool>(type: "bit", nullable: false, defaultValue: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Product_Category_UID", x => x.uid)
.Annotation("SqlServer:Clustered", false);
table.UniqueConstraint("IX_Product_Category_Code", x => x.code)
.Annotation("SqlServer:Clustered", true);
table.ForeignKey(
name: "FK_Category_Product_Family",
column: x => x.fFamilyCode,
principalSchema: "Product",
principalTable: "Family",
principalColumn: "code",
onDelete: ReferentialAction.Restrict);
});
migrationBuilder.CreateIndex(
name: "IX_Category_fFamilyCode",
schema: "Product",
table: "Category",
column: "fFamilyCode");
And... finally the DDL script output from the migration:
CREATE TABLE [Product].[Family] (
[uid] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
[code] int NOT NULL IDENTITY,
[description] nvarchar(50) NOT NULL,
[created] datetime NOT NULL DEFAULT ((getdate())),
[updated] datetime NOT NULL DEFAULT ((getdate())),
[inactive] bit NOT NULL DEFAULT CAST(0 AS bit),
CONSTRAINT [PK_Product_Family_UID] PRIMARY KEY NONCLUSTERED ([uid]),
CONSTRAINT [IX_Product_Family_Code] UNIQUE CLUSTERED ([code])
);
GO
CREATE TABLE [Product].[Category] (
[uid] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
[code] int NOT NULL IDENTITY,
[description] nvarchar(50) NOT NULL,
[fFamilyCode] int NOT NULL,
[created] datetime NOT NULL DEFAULT ((getdate())),
[updated] datetime NOT NULL DEFAULT ((getdate())),
[inactive] bit NOT NULL DEFAULT CAST(0 AS bit),
CONSTRAINT [PK_Product_Category_UID] PRIMARY KEY NONCLUSTERED ([uid]),
CONSTRAINT [IX_Product_Category_Code] UNIQUE CLUSTERED ([code]),
CONSTRAINT [FK_Category_Product_Family] FOREIGN KEY ([fFamilyCode]) REFERENCES [Product].[Family] ([code]) ON DELETE NO ACTION
);
GO
CREATE INDEX [IX_Category_fFamilyCode] ON [Product].[Category] ([fFamilyCode]);
GO
As you can see, the UNIQUE CLUSTERED INDEX ON [Product].[Category]
is missing in my case from the DDL compared to what you got.
Anyway, I ran the command to update the database. And it looks like this:
Conclusions:
[Product].[Family]
table as previously it was getting created.[Product].[Category]
table targeting the FK
(as shown in the screenshot).[Product].[Category]
table via SSMS and script it as CREATE TO -> New query window
to see the actual script behind it, it looks like this:/****** Object: Table [Product].[Category] Script Date: 14-03-2024 11:31:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Product].[Category](
[uid] [uniqueidentifier] NOT NULL,
[code] [int] IDENTITY(1,1) NOT NULL,
[description] [nvarchar](50) NOT NULL,
[fFamilyCode] [int] NOT NULL,
[created] [datetime] NOT NULL,
[updated] [datetime] NOT NULL,
[inactive] [bit] NOT NULL,
CONSTRAINT [PK_Product_Category_UID] PRIMARY KEY NONCLUSTERED
(
[uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_Product_Category_Code] UNIQUE CLUSTERED
(
[code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Product].[Category] ADD DEFAULT (newsequentialid()) FOR [uid]
GO
ALTER TABLE [Product].[Category] ADD DEFAULT (getdate()) FOR [created]
GO
ALTER TABLE [Product].[Category] ADD DEFAULT (getdate()) FOR [updated]
GO
ALTER TABLE [Product].[Category] ADD DEFAULT (CONVERT([bit],(0))) FOR [inactive]
GO
ALTER TABLE [Product].[Category] WITH CHECK ADD CONSTRAINT [FK_Category_Product_Family] FOREIGN KEY([fFamilyCode])
REFERENCES [Product].[Family] ([code])
GO
ALTER TABLE [Product].[Category] CHECK CONSTRAINT [FK_Category_Product_Family]
GO
... there is no CONSTRAINT
that would generate the IX_Category_fFamilyCode
non-unique, non-clustered index.
First, let remove SubCategories
and Products
collections, since they reference entities not included in the post, and concentrate only on Family
and Category
models and their relationship.
Now some facts:
HasPrincipalKey
API, if the referenced field is not primary or alternate key, EF will create alternate key for you, with default conventional constraint/index names.The last is what is causing the unexpected unique constraint and index in you case. Because of this line
entity.HasOne(d => d.FFamilyCodeNavigation).WithMany(p => p.Categories)
.HasForeignKey(d => d.FFamilyCode)
.HasPrincipalKey(d => d.Code) // <--
.OnDelete(DeleteBehavior.Restrict)
.HasConstraintName("FK_Category_Product_Family");
Even though you have configured unique index, it is not using the conventional name which in this particular case is ""AK_Family_code", so EF creates new one "for you".
One may consider this as bug, but it is what it is, and there is a simple solution - instead of letting EF create alternate key for you, create and configure explicitly such key instead of unique index.
In your case, it's a matter of replacing
entity.HasIndex(e => e.Code, "IX_Product_Family_Code")
.IsClustered();
with
entity.HasAlternateKey(e => e.Code)
.HasName("IX_Product_Family_Code")
.IsClustered();
Now the generated migration should be like this
migrationBuilder.CreateTable(
name: "Family",
schema: "Product",
columns: table => new
{
uid = table.Column<Guid>(type: "uniqueidentifier", nullable: false, defaultValueSql: "(newsequentialid())"),
code = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
description = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false),
created = table.Column<DateTime>(type: "datetime", nullable: false, defaultValueSql: "(getdate())"),
updated = table.Column<DateTime>(type: "datetime", nullable: false, defaultValueSql: "(getdate())"),
inactive = table.Column<bool>(type: "bit", nullable: false, defaultValue: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Product_Family_UID", x => x.uid)
.Annotation("SqlServer:Clustered", false);
table.UniqueConstraint("IX_Product_Family_Code", x => x.code)
.Annotation("SqlServer:Clustered", true);
});
migrationBuilder.CreateTable(
name: "Category",
schema: "Product",
columns: table => new
{
uid = table.Column<Guid>(type: "uniqueidentifier", nullable: false, defaultValueSql: "(newsequentialid())"),
code = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
description = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false),
fFamilyCode = table.Column<int>(type: "int", nullable: false),
created = table.Column<DateTime>(type: "datetime", nullable: false, defaultValueSql: "(getdate())"),
updated = table.Column<DateTime>(type: "datetime", nullable: false, defaultValueSql: "(getdate())"),
inactive = table.Column<bool>(type: "bit", nullable: false, defaultValue: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Product_Category_UID", x => x.uid)
.Annotation("SqlServer:Clustered", false);
table.ForeignKey(
name: "FK_Category_Product_Family",
column: x => x.fFamilyCode,
principalSchema: "Product",
principalTable: "Family",
principalColumn: "code",
onDelete: ReferentialAction.Restrict);
});
migrationBuilder.CreateIndex(
name: "IX_Category_fFamilyCode",
schema: "Product",
table: "Category",
column: "fFamilyCode");
migrationBuilder.CreateIndex(
name: "IX_Product_Category_Code",
schema: "Product",
table: "Category",
column: "code",
unique: true)
.Annotation("SqlServer:Clustered", true);
and DDL (extracted with Script-Migration
command):
CREATE TABLE [Product].[Family] (
[uid] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
[code] int NOT NULL IDENTITY,
[description] nvarchar(50) NOT NULL,
[created] datetime NOT NULL DEFAULT ((getdate())),
[updated] datetime NOT NULL DEFAULT ((getdate())),
[inactive] bit NOT NULL DEFAULT CAST(0 AS bit),
CONSTRAINT [PK_Product_Family_UID] PRIMARY KEY NONCLUSTERED ([uid]),
CONSTRAINT [IX_Product_Family_Code] UNIQUE CLUSTERED ([code])
);
GO
CREATE TABLE [Product].[Category] (
[uid] uniqueidentifier NOT NULL DEFAULT ((newsequentialid())),
[code] int NOT NULL IDENTITY,
[description] nvarchar(50) NOT NULL,
[fFamilyCode] int NOT NULL,
[created] datetime NOT NULL DEFAULT ((getdate())),
[updated] datetime NOT NULL DEFAULT ((getdate())),
[inactive] bit NOT NULL DEFAULT CAST(0 AS bit),
CONSTRAINT [PK_Product_Category_UID] PRIMARY KEY NONCLUSTERED ([uid]),
CONSTRAINT [FK_Category_Product_Family] FOREIGN KEY ([fFamilyCode]) REFERENCES [Product].[Family] ([code]) ON DELETE NO ACTION
);
GO
CREATE INDEX [IX_Category_fFamilyCode] ON [Product].[Category] ([fFamilyCode]);
GO
CREATE UNIQUE CLUSTERED INDEX [IX_Product_Category_Code] ON [Product].[Category] ([code]);
GO
which is what is expected. Problem solved.
Just keep in mind that even though database allow modifying Family.Code
column, EF core won't because of the alternate key requirements/constraints.
Most of this is explained with examples in Alternate Keys section of the official EF Core documentation.
Update: Regarding the "extra" non unique index on FK column ("IX_Category_fFamilyCode").
Creating non unique index for FK columns is considered a good practice, as it helps database enforcing the FK constraint, performing SQL joins for querying child "collection", cascade delete (when used) etc. EF is strictly following that practice and does not allow you to remove such indexes even with fluent/model APIs. And probably this is how it should be - these indexes really help.