使用Code First执行Update-Database时遇外键约束冲突异常求解决

模型代码 #

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;

namespace web_template_001.Models
{
    public class Movie
    {
        [Required]
        public int Id { get; set; }
        [Required]
        [StringLength(25)]
        public string Title { get; set; }
        [Required]
        public Genre Genre { get; set; }
        public byte GenreId { get; set; }
        public DateTime DateAdded { get; set; }
        public DateTime ReleaseDate { get; set; }
        public byte NumberInStock { get; set; }
    }

    public class Genre
    {
        [Required]
        public byte Id { get; set; }
        [Required]
        [StringLength(25)]
        public string Name { get; set; }
    }
}

迁移代码 #

namespace web_template_001.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
    
    public partial class ModifiedMovieProperties : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Genres",
                c => new
                    {
                        Id = c.Byte(nullable: false),
                        Name = c.String(nullable: false, maxLength: 25),
                    })
                .PrimaryKey(t => t.Id);
            
            AddColumn("dbo.Movies", "GenreId", c => c.Byte(nullable: false));
            AddColumn("dbo.Movies", "DateAdded", c => c.DateTime(nullable: false));
            AddColumn("dbo.Movies", "ReleaseDate", c => c.DateTime(nullable: false));
            AddColumn("dbo.Movies", "NumberInStock", c => c.Byte(nullable: false));
            CreateIndex("dbo.Movies", "GenreId");
            AddForeignKey("dbo.Movies", "GenreId", "dbo.Genres", "Id", cascadeDelete: true);
        }
        
        public override void Down()
        {
            DropForeignKey("dbo.Movies", "GenreId", "dbo.Genres");
            DropIndex("dbo.Movies", new[] { "GenreId" });
            DropColumn("dbo.Movies", "NumberInStock");
            DropColumn("dbo.Movies", "ReleaseDate");
            DropColumn("dbo.Movies", "DateAdded");
            DropColumn("dbo.Movies", "GenreId");
            DropTable("dbo.Genres");
        }
    }
}

执行Update-Database时的错误 #

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Movies_dbo.Genres_GenreId". The conflict occurred in database "aspnet-web_template_001-20230102051540", table "dbo.Genres", column 'Id'.

已尝试删除迁移重新创建、重启Visual Studio，问题仍未解决，此前用相同方法关联其他模型外键正常，寻求解决方案。

错误核心原因：添加非空GenreId外键列时，现有Movies表数据会被自动填充Byte类型默认值0，但刚创建的Genres表没有Id为0的记录，导致外键约束冲突。

方法一：修改迁移代码分阶段执行 #

先添加允许为空的外键列，插入默认Genre数据，再更新Movies表的GenreId为合法值，最后修改列为非空并添加约束：

public override void Up()
{
    // 1. 创建Genres表并插入默认数据
    CreateTable(
        "dbo.Genres",
        c => new
            {
                Id = c.Byte(nullable: false),
                Name = c.String(nullable: false, maxLength: 25),
            })
        .PrimaryKey(t => t.Id);
    
    // 插入一条合法Genre记录
    Sql("INSERT INTO dbo.Genres (Id, Name) VALUES (1, 'Default Genre')");

    // 2. 添加允许为空的GenreId列及其他列
    AddColumn("dbo.Movies", "GenreId", c => c.Byte());
    AddColumn("dbo.Movies", "DateAdded", c => c.DateTime(nullable: false));
    AddColumn("dbo.Movies", "ReleaseDate", c => c.DateTime(nullable: false));
    AddColumn("dbo.Movies", "NumberInStock", c => c.Byte(nullable: false));

    // 3. 更新现有Movies的GenreId为合法值
    Sql("UPDATE dbo.Movies SET GenreId = 1 WHERE GenreId IS NULL");

    // 4. 修改GenreId列为非空，添加索引和外键
    AlterColumn("dbo.Movies", "GenreId", c => c.Byte(nullable: false));
    CreateIndex("dbo.Movies", "GenreId");
    AddForeignKey("dbo.Movies", "GenreId", "dbo.Genres", "Id", cascadeDelete: true);
}

方法二：清空测试环境Movies数据（仅适用于测试场景） #

若数据库中Movies数据无保留价值，可先删除所有记录再创建外键：

public override void Up()
{
    CreateTable(
        "dbo.Genres",
        c => new
            {
                Id = c.Byte(nullable: false),
                Name = c.String(nullable: false, maxLength: 25),
            })
        .PrimaryKey(t => t.Id);
    
    Sql("INSERT INTO dbo.Genres (Id, Name) VALUES (1, 'Default Genre')");
    
    // 删除现有Movies数据
    Sql("DELETE FROM dbo.Movies");

    AddColumn("dbo.Movies", "GenreId", c => c.Byte(nullable: false));
    AddColumn("dbo.Movies", "DateAdded", c => c.DateTime(nullable: false));
    AddColumn("dbo.Movies", "ReleaseDate", c => c.DateTime(nullable: false));
    AddColumn("dbo.Movies", "NumberInStock", c => c.Byte(nullable: false));
    CreateIndex("dbo.Movies", "GenreId");
    AddForeignKey("dbo.Movies", "GenreId", "dbo.Genres", "Id", cascadeDelete: true);
}

修改完成后，重新执行Update-Database命令即可完成迁移。

内容的提问来源于stack exchange，提问作者Joshua Mitchum