更改生产数据库中的主键数据类型 [英] Changing primary key data type in Production DB

问题描述

我使用的是 EF 代码优先.我需要更改生产数据库中一张表的主键数据类型.

I am using EF code-first. I need to change the datatype of primary key of one table in our production database.

public class Course 
{
   [Key]
   public Guid Id {get; set;}    //This needs to be changed to int

   public string Name {get;set;}

   public virtual ICollection<Group> Groups {get;set;}
}

public class Group
{
   [Key]
   public Guid Id {get; set;}

   public string Name {get;set;}

   public virtual ICollection<Course> Courses{get;set;}
}

由于上面是Many-Many关系，EF已经自动创建了一个连接表GroupCourses PK &FK 同时作为 Group_Id 和 Course_Id.到目前为止还不错.

As the above is a Many-Many relationship, EF has created a join table GroupCourses automatically with PK & FK as both Group_Id and Course_Id. Which is fine until now.

现在我需要将 Course 实体中的主键从 Guid 更改为 int.

Now I need to change the primary key in Course entity to int from Guid.

我将数据类型从 Guid 更改为 int 并成功创建了新的迁移.但是当我尝试运行命令 update-database 时，我遇到了错误.

I changed the datatype from Guid to int and successfully created a new migration. But when I try to run the command update-database, I am getting below error.

操作数类型冲突:uniqueidentifier 与查询中的 int 不兼容

Operand type clash: uniqueidentifier is incompatible with int in query

我不确定如何解决上述错误.

I am not sure how to fix the above error.

另外，我认为在不丢失数据的情况下更改主键字段的数据类型是不可能的(当然，我们可以手动从备份中复制).我希望我错了.

Also, I think it's impossible to change the datatype of a primary key field without losing data (of course, we can copy from backup manually). I hope I am wrong.

有人可以告诉我有没有更好的方法可以在不丢失数据的情况下实现这一点?

Can someone please tell me is there any better way of achieving this without losing data ?

注意:我浏览过其他与 SO 相关的帖子和​​其他互联网论坛，但没有运气.

NOTE : I have gone through other SO posts related and other internet forums but no luck.

推荐答案

您收到类型冲突错误，因为生成的迁移尝试直接在受影响的列上执行 ALTER COLUMN 以将类型从 Guid 到 integer，这是不可能的，因为 Guid 值不能隐式转换为 integer 值.

You get the type clash error because the generated migration tries to do directly ALTER COLUMN on the affected columns to change the type from Guid to integer, and it is not possible because the Guid values can not be implicitly converted to integer values.

您必须手动修改迁移.删除 AlterColumn 行.为了在不丢失数据的情况下迁移现有课程 ID，您可以临时重命名现有 Guid 列(添加后缀 _Old)，使用正确的新名称和类型创建新列，使用 UPDATE 和 SELECT 用正确的值填充关系表中的内容，最后删除旧列:

You have to manually modify your migration. Remove the AlterColumn lines. In order to migrate your existing course ids without data loss you can temporarily rename the existing Guid columns (adding a suffix _Old), create new columns with the right new name and type, fill the contents in the relationship table with the right values using an UPDATE with a SELECT, and finally delete the old columns:

public override void Up()
{
    DropForeignKey("dbo.GroupCourses", "Course_Id", "dbo.Courses");
    DropIndex("dbo.GroupCourses", new[] { "Course_Id" });
    DropPrimaryKey("dbo.Courses");
    DropPrimaryKey("dbo.GroupCourses");

    //Removed: AlterColumn("dbo.Courses", "Id", c => c.Int(nullable: false, identity: true));
    RenameColumn("dbo.Courses", "Id", "Id_Old"); //Added
    AddColumn("dbo.Courses", "Id", c => c.Int(nullable: false, identity: true)); //Added

    //Removed: AlterColumn("dbo.GroupCourses", "Course_Id", c => c.Int(nullable: false));
    RenameColumn("dbo.GroupCourses", "Course_Id", "Course_Id_Old"); //Added
    AddColumn("dbo.GroupCourses", "Course_Id", c => c.Int(nullable: false)); //Added
    Sql(@"UPDATE gc SET gc.Course_Id = c.Id " 
            + "FROM dbo.GroupCourses as gc " 
            + "INNER JOIN dbo.Courses as c ON gc.Course_Id_Old = c.id_Old"); //Added

    DropColumn("dbo.GroupCourses", "Course_Id_Old"); //Added
    DropColumn("dbo.Courses", "Id_Old");    //Added

    AddPrimaryKey("dbo.Courses", "Id");
    AddPrimaryKey("dbo.GroupCourses", new[] { "Group_Id", "Course_Id" });
    CreateIndex("dbo.GroupCourses", "Course_Id");
    AddForeignKey("dbo.GroupCourses", "Course_Id", "dbo.Courses", "Id", cascadeDelete: true);
}

我在代码中用注释 //Added 和 //Removed 标记了我更改的行.其余代码是在模型中将属性类型更改为 int 后使用 Add-Migration 生成的迁移中的原始代码.

I marked in the code the lines I changed with the comments //Added and //Removed. The rest of the code is the original one in the migration as generated with Add-Migration after changing the property type to int in the model.

您只需用UPDATE 填充关系表中列中的值.Courses.Id 列中的值将自动生成，因为它是一个 identity 列.

You only have to fill with an UPDATE the values in the column in the relationship table. The values in Courses.Id column will be auto-generated, as it is an identity column.

您还应该找出 Down() 方法并实现相反的过程，或者如果您真的永远不会将数据库降级到以前的迁移，则保持原样.

You should also figure out the Down() method and implement the reverse process, or leave it as it is if you really are never going to downgrade your database to a previous migration.

您也可以通过在迁移和播种器之间拆分代码来做到这一点.从概念上讲，它会更正确，因为您应该使用迁移来修改模式，使用播种器来修改数据.但我认为这种方式更容易，您只需要一次迁移，而使用播种器方法则需要两次迁移:一次包含上面包含的所有代码，除了 UPDATE 和删除_Old 列，另一个只是删除了 _Old 列.UPDATE 部分将在播种器中实现.

You can also do this by splitting the code between the migration and the seeder. It would be more correct conceptually, as you are supposed to use the migrations for schema modifications and the seeder for data modifications. But I think it is easier this way, and you need only one migration, while with the seeder approach you would need two migrations: one with all the code included above except the UPDATE and the sentences that drop the _Old columns, and another one just with the dropping of the _Old columns. The UPDATE part would be implemented in the seeder.

警告:UPDATE 语句中包含的 SQL 代码适用于 Sql Server，但可能不适用于其他数据库引擎.这是使用迁移而不是播种机的另一个缺点.

A warning: the SQL code included in the UPDATE sentence works with Sql Server but might not work with other database engines. It is another downside of using the migration instead of the seeder.

这篇关于更改生产数据库中的主键数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！