Migrations creates extra keys and indexes

Question

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?

Edit upon the answer received from Ivan

Please allow me to break your answer:

1. 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?

2. 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.

3. 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.

Anyhow, let's dive into your proposed solution

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:

1. Yeah, I got rid of the extra index created on [Product].[Family] table as previously it was getting created.
2. I still get the extra index on [Product].[Category] table targeting the FK (as shown in the screenshot).
3. What's weird though is, if I right click on [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.

4. Am I missing anything from your recommendation?

Answer 1

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:

1. 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.
2. 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.
3. 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.

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.