2

Simplest 2 table definition in EF code-first with foreign key.

public class Blog {
    [Key]
    public int BlogId { get; set; }
    public string Name { get; set; }

    public virtual List<Post> Posts { get; set; }
}

public class Post {
    [Key, Column(Order = 0)]
    public int BlogId { get; set; }

    [Key, Column(Order = 1)]
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public virtual Blog Blog { get; set; }
}

Generates

CREATE TABLE [dbo].[Blogs] (
    [BlogId] [int] NOT NULL IDENTITY,
    [Name] [nvarchar](max),
    CONSTRAINT [PK_dbo.Blogs] PRIMARY KEY ([BlogId])
)
CREATE TABLE [dbo].[Posts] (
    [BlogId] [int] NOT NULL,
    [PostId] [int] NOT NULL,
    [Title] [nvarchar](max),
    [Content] [nvarchar](max),
    CONSTRAINT [PK_dbo.Posts] PRIMARY KEY ([BlogId], [PostId])
)

CREATE INDEX [IX_BlogId] ON [dbo].[Posts]([BlogId])

ALTER TABLE [dbo].[Posts] 
   ADD CONSTRAINT [FK_dbo.Posts_dbo.Blogs_BlogId] 
   FOREIGN KEY ([BlogId]) REFERENCES [dbo].[Blogs] ([BlogId]) 
       ON DELETE CASCADE

BlogId is the 1st column in composite index to maintain primary key, hence IX_BlogId is redundant and won’t be used ever.

What was the point creating it?

There were similar questions discussing indexing foreign key column, but not redundant indexes: Entity Framework Indexing ALL foreign key columns

Entity Framework Code First Foreign Key adding Index as well

IMO, there is no universal answer, i.e. "it depends". It should be an easy way to disable index creation.

Community
  • 1
  • 1
Same old guy...
  • 305
  • 1
  • 3
  • 15
  • Yes, it is part of the primary key. However, in some DBMS primary key is just a UNIQUE constraint on the column, it doesn't necessarily mean it is also an index on the column. – DevilSuichiro Sep 21 '15 at 06:03
  • Try to follow your logic one step further: _if_ “a DBMS primary key is just a UNIQUE constraint on the column, it doesn't necessarily mean it is also an index on the column”, _than_ layer, translating CodeFirst to the DBMS, creates index to maintain primary key. Since it doesn’t do it - it’s aware, that MS SQL primary key has index already. I.e. it knows the foreign key column has index already, yet creates another one "just in case". – Same old guy... Sep 26 '15 at 15:31
  • actually... nope. this is not the case. in MS SQL, primary key is a unique ASC non-clustered index. but not in all DBMS. EF itself won't create indices that are not already IMPLIED by the constraints referred to in the column descriptions. – DevilSuichiro Sep 27 '15 at 13:00
  • IMO, primary key is clustered index by default in MS SQL. I cannot tell for older versions, tho. Still, imports part is, EF MS SQL driver knows NOT to create separate index fro PK. I hope, the EF driver for another DB, where PK is not backed up by index, will create one. "EF itself won't create indices that are not already IMPLIED by the constraints referred to in the column descriptions." Again, by EF you mean some EF database driver, which has some knowledge about DB is serves, but not smart enough, to see if index is there already, right? – Same old guy... Sep 29 '15 at 19:27
  • yup. The EF database provider creates the sql queries for you, also for creation of the tables. Here it seems MS SQL provider will "by default" create an index on the primary key on first position upon creation. it is actually expected for it to create an index on the PK column (or at least one of them) - if not noticing the composite PK (and therefore implied add. index) is by design or a mistake, I don't know. – DevilSuichiro Sep 29 '15 at 20:21

0 Answers0