EF Core 主从表修改主键类型步骤

1. 背景

有两张表Blog与Post

classDiagram class Blog { +Guid Id +String Name +DateTime CreateTime +int Order +List<Post> Posts } class Post { +String Id +String Content +Guid BlogId +Blog Blog } Blog --* Post

最开始的设计Blog表的主键是Guid的，但是后期想改成String，又涉及到Post表的修改，步骤记录如下

2. 修改步骤

2.1 修改Post表的外键约束

将之前的外键约束 Cascade 修改为 NoAction

builder.HasOne(b => b.Blog).WithMany(p => p.Posts)
                .HasForeignKey(p => p.BlogId)
                .OnDelete(DeleteBehavior.NoAction);

// 生成的 Migration
public partial class ModifyRelationshipFK : Migration
{
  protected override void Up(MigrationBuilder migrationBuilder)
  {
      migrationBuilder.DropForeignKey(
          name: "FK_Posts_Blogs_BlogId",
          table: "Posts");

      migrationBuilder.AddForeignKey(
          name: "FK_Posts_Blogs_BlogId",
          table: "Posts",
          column: "BlogId",
          principalTable: "Blogs",
          principalColumn: "Id");
  }

  protected override void Down(MigrationBuilder migrationBuilder)
  {
      migrationBuilder.DropForeignKey(
          name: "FK_Posts_Blogs_BlogId",
          table: "Posts");

      migrationBuilder.AddForeignKey(
          name: "FK_Posts_Blogs_BlogId",
          table: "Posts",
          column: "BlogId",
          principalTable: "Blogs",
          principalColumn: "Id",
          onDelete: ReferentialAction.Cascade);
  }
}

2.2 修改主表类型与主键约束

// Blog 修改Id类型为string，Post修改BlogId类型为string
// Post 修改主键约束
builder.HasAlternateKey(b => b.Id).HasName("PK_Blogs_Guid2String");

// Post表修改外键约束
builder.HasOne(b => b.Blog).WithMany(p => p.Posts)
                .HasForeignKey(p => p.BlogId)
                .OnDelete(DeleteBehavior.Cascade);
// 生成的 Migration
public partial class ModifyBlogIdType : Migration
{
  protected override void Up(MigrationBuilder migrationBuilder)
  {
      migrationBuilder.DropForeignKey(
          name: "FK_Posts_Blogs_BlogId",
          table: "Posts");

      migrationBuilder.DropPrimaryKey(
          name: "PK_Blogs",
          table: "Blogs");

      migrationBuilder.AlterColumn<string>(
          name: "BlogId",
          table: "Posts",
          type: "nvarchar(100)",
          maxLength: 100,
          nullable: true,
          oldClrType: typeof(Guid),
          oldType: "uniqueidentifier",
          oldMaxLength: 100);

      migrationBuilder.AlterColumn<string>(
          name: "Id",
          table: "Blogs",
          type: "nvarchar(100)",
          maxLength: 100,
          nullable: false,
          oldClrType: typeof(Guid),
          oldType: "uniqueidentifier",
          oldMaxLength: 100);

      migrationBuilder.AddPrimaryKey(
          name: "PK_Blogs_To_String",
          table: "Blogs",
          column: "Id");

      migrationBuilder.AddForeignKey(
          name: "FK_Posts_Blogs_BlogId",
          table: "Posts",
          column: "BlogId",
          principalTable: "Blogs",
          principalColumn: "Id",
          onDelete: ReferentialAction.Cascade);
  }

  protected override void Down(MigrationBuilder migrationBuilder)
  {
      migrationBuilder.DropForeignKey(
          name: "FK_Posts_Blogs_BlogId",
          table: "Posts");

      migrationBuilder.DropPrimaryKey(
          name: "PK_Blogs_Guid2String",
          table: "Blogs");

      migrationBuilder.AlterColumn<Guid>(
          name: "BlogId",
          table: "Posts",
          type: "uniqueidentifier",
          maxLength: 100,
          nullable: false,
          defaultValue: new Guid("00000000-0000-0000-0000-000000000000"),
          oldClrType: typeof(string),
          oldType: "nvarchar(100)",
          oldMaxLength: 100,
          oldNullable: true);

      migrationBuilder.AlterColumn<Guid>(
          name: "Id",
          table: "Blogs",
          type: "uniqueidentifier",
          maxLength: 100,
          nullable: false,
          oldClrType: typeof(string),
          oldType: "nvarchar(100)",
          oldMaxLength: 100);

      migrationBuilder.AddPrimaryKey(
          name: "PK_Blogs",
          table: "Blogs",
          column: "Id");

      migrationBuilder.AddForeignKey(
          name: "FK_Posts_Blogs_BlogId",
          table: "Posts",
          column: "BlogId",
          principalTable: "Blogs",
          principalColumn: "Id");
  }
}

3. 总结

关键点主要在于子表的外键约束与主表的主键约束，首先要删除子表的外键约束并重建，其次要重建主表的主键约束；