更新 - 數據庫失敗:“索引'IX_Task_UserId'依賴於列'UserId'”

asp.net asp.net-core asp.net-mvc entity-framework entity-framework-core

[required]註釋添加到3個模型上的2個屬性並運行add-migration ,運行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'.

我的up()遷移看起來像這樣:

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);
    }

我不確定錯誤所引用的索引,也不完全理解為什麼要刪除外鍵(同樣,我所做的只是使一些屬性不可為空)。謝謝你的幫助。

一般承認的答案

我所做的就是讓一些屬性不可空

好吧,通常將非可空(必需)表列轉換為可空(可選)沒有問題,但不是相反,所以提前仔細考慮是件好事。

這裡的“小”問題是大多數屬性都是外鍵,這使得轉換更加複雜。 EF Core自動生成的遷移嘗試通過在更改列之前刪除FK約束並在之後重新創建它來正確處理。不幸的是,FK約束通常具有關聯索引(如果您發現為例如“FK_Task_AspNetUsers_UserId”創建的遷移),您應該看到它還創建了一個索引“IX_Task_UserId”,並且他們忘記刪除並重新創建索引,這導致你得到的例外(它來自數據庫)。

因此,您需要手動修復生成的遷移(您還可以考慮將錯誤報告發佈到EF Core存儲庫)。

為此,請在第一個AlterColumn調用之前插入以下內容:

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

最後一次AlterColumn調用之後的以下內容:

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

這個問題應該解決。

請注意,您可能需要對遷移中包含的其他表執行類似操作,即類似這樣的操作(確保從相應的初始遷移中驗證名稱):

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

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

接著:

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

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


Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow