在生产数据库中更改主键数据类型 [英] 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;}
}

由于上述关系是多对多关系，因此EF通过PK& amp;自动创建了联接表GroupCourses. 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屋！