1

I use SQL Server 2017 (v14.0.2027.2) and EF Core 2.1.

After I added migration on this code:

public class TblTrack
{
    public long Id { get; set; }
    ...
}

public class TblProduct
{
    public long Id { get; set; }
    ...
}

public class TblProductItem
{
    [Key]
    [Required]
    public long ProductId { get; set; }

    [Key]
    [Required]
    public long TrackId { get; set; }

    // Navigation properties
    public TblProduct Product { get; set; }
    public TblTrack Track { get; set; }
}

Ef Core creates only TrackId index

migrationBuilder.CreateIndex(
name: "IX_tbl_ProductItems_TrackId",
table: "tbl_ProductItems",
column: "TrackId");

Why was an index created for TrackId but not for ProductId?

Murrelken
  • 43
  • 6
  • 1
    Side note: FK/PK relationships work best if both key columns involved are the **same** datatype - you have `long` for the PK in the two tables, but `int` for FK in the referencing table - this should really be the same (either both `int`, or both `long`) – marc_s Nov 02 '19 at 10:04
  • 1
    Also: the spelling on these key fields: you have `ID` and `TrackID` for the key that gets generated (where `ID` is all uppercase), and you have `Id` and `ProductID` where again you're not being consistent - once you spell with both letters in uppercase, one is mixed case ...... try to be **consistent** - either spell `ID` always - or use `Id` always - but **do NOT MIX** .... – marc_s Nov 02 '19 at 10:06

1 Answers1

1

Why was an index created for TrackId but not for ProductId?

This is actually very clever, and correct behavior by EF. The Primary Key on TblProductItem is (ProductId,TrackId). So you already have an index supporting that Foreign Key (Since the FK columns are the leading columns in that index). Only the trailing PK column (TrackId) needs a seperate index to support the Foreign Key.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • I do not clearly understand why composite PK is working like this. I mean why one column of such PK is "leading" and others are "trailing". Could you please attach some links that explain how it works in a database and what's the advantages? I appreciate your answer anyway. – Murrelken Nov 03 '19 at 08:31
  • See https://stackoverflow.com/questions/795031/how-do-composite-indexes-work – David Browne - Microsoft Nov 03 '19 at 14:22