Update-Database failed: "The index 'IX_Task_UserId' is dependent on column 'UserId'"

Question

After adding a [required] annotation to 2 properties on 3 models and running add-migration, I get the following error when running update-database.

ALTER TABLE ALTER COLUMN UserId failed because one or more objects access this column. The index 'IX_Task_UserId' is dependent on column 'UserId'.

My up() migration looks like this:

protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropForeignKey(
            name: "FK_TaskList_AspNetUsers_UserId",
            table: "TaskList");

        migrationBuilder.DropForeignKey(
            name: "FK_Template_AspNetUsers_UserId",
            table: "Template");

        migrationBuilder.DropForeignKey(
            name: "FK_Task_AspNetUsers_UserId",
            table: "Task");

        migrationBuilder.AlterColumn<string>(
            name: "UserId",
            table: "Task",
            nullable: false);

        migrationBuilder.AlterColumn<string>(
            name: "TaskName",
            table: "Task",
            nullable: false);

        migrationBuilder.AlterColumn<string>(
            name: "UserId",
            table: "Template",
            nullable: false);

        migrationBuilder.AlterColumn<string>(
            name: "TemplateName",
            table: "Template",
            nullable: false);

        migrationBuilder.AlterColumn<string>(
            name: "UserId",
            table: "TaskList",
            nullable: false);

        migrationBuilder.AlterColumn<string>(
            name: "ListName",
            table: "TaskList",
            nullable: false);

        migrationBuilder.AddForeignKey(
            name: "FK_TaskList_AspNetUsers_UserId",
            table: "TaskList",
            column: "UserId",
            principalTable: "AspNetUsers",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);

        migrationBuilder.AddForeignKey(
            name: "FK_Template_AspNetUsers_UserId",
            table: "Template",
            column: "UserId",
            principalTable: "AspNetUsers",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);

        migrationBuilder.AddForeignKey(
            name: "FK_Task_AspNetUsers_UserId",
            table: "Task",
            column: "UserId",
            principalTable: "AspNetUsers",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
    }

I'm not sure what index the error refers to, nor do I fully understand why foreign keys are being dropped (again, all I did was make a few properties non-nullable). Thanks for any help.

Answer 1

all I did was make a few properties non-nullable

Well, usually there is no problem converting non-nullable (required) table column to nullable (optional), but not the opposite, so it's good to think carefully in advance.

The "little" problem here is that most of the few properties are foreign keys, which additionally complicates the conversion. EF Core auto generated migration tries to correctly handle that by removing the FK constraints before altering the column and recreating them after. Unfortunately FK constraint usually has associated index (if you find the migration that created for instance "FK_Task_AspNetUsers_UserId", you should see that it also creates an index "IX_Task_UserId"), and they forget to drop and recreate the index as well, which leads to the exception you are getting (it comes from database).

So you need to fix the generated migration manually (you might also consider posting a bug report to EF Core repository).

To do that, insert the following before the first AlterColumn call:

migrationBuilder.DropIndex(
    name: "IX_Task_UserId",
    table: "Task");

and the following after the last AlterColumn call:

migrationBuilder.CreateIndex(
    name: "IX_Task_UserId",
    table: "Task",
    column: "UserId");

and the issue should be solved.

Note that you might need to do similar for the other tables included in the migration as well, i.e. something like this (make sure to verify the names from the corresponding initial migration):

migrationBuilder.DropIndex(
    name: "IX_TaskList_UserId",
    table: "TaskList");

migrationBuilder.DropIndex(
    name: "IX_Template_UserId",
    table: "Template");

and then:

migrationBuilder.CreateIndex(
    name: "IX_TaskList_UserId",
    table: "TaskList",
    column: "UserId");

migrationBuilder.CreateIndex(
    name: "IX_Template_UserId",
    table: "Template",
    column: "UserId");