To solve this problem, I used the Sql() method in the Up() and Down() methods of the migration class. The SQL command line in the Up() method removes the primary key constraint in the identifier column, discards the identifier column of type int , and then adds a new identifier column of type Guid . The Down() method does the same, but the Guid column drops and adds a new int column.
I found several solutions in Stack Overflow that allow a "column type change" by running the SQL command in the query window. To post a comment,
We are just trying to maintain a clean / transparent migration path for tracking when we have done something that is not always easy with SQL.
I used the SQL commands in the Up() and Down() migration methods. For me, this solution works well in my projects.
The solution at the bottom of this answer was built from several questions / answers about stack overflow. Skip this for code only. Here are the detailed details.
Using SQL Commands in a Migration Class
I could not find a solution that used Entity Framework migration methods such as AlterColumn() and DropColumn() .
Instead of using a combination of migration methods and commands in the Sql() method, I used all the SQL commands in the row in the Sql() migration method. Using all SQL commands has simplified testing in the query window in Visual Studio or SQL Server Management Studio.
The answer from Uchitha gave the initial steps to add the Sql() method within the desired migration class.
- Generate a migration class using Add-Migration
- Modify the class using code similar to above
- Starting migration using Update-Database
A sample Sql() method in the answer looks like this:
Sql("UPDATE dbo.YourTable SET Column1 = 'VALUE1' ");
Changing a column type - general steps
I used the answer with the JustAnotherUserYouMayKnow command to start with the steps to change the type of the column. I did not follow this explicitly, but it provided only the basic structure of the need to reset the column and recreate it.
- Add a new column with a new type
- Use
Sql() to migrate data from source column using update statement - Delete old column
- Rename New Column
Serial GUIDs
The answer from Icarus provided the ALTER TABLE statement with newsequentialid() to generate sequential GUIDs according to your statement:
I would like it to be a sequentially increased Guid.
ALTER TABLE your_table ADD your_column UNIQUEIDENTIFIER DEFAULT newsequentialid() NOT null
Note the "Johan" privacy considerations in the comment section of the "Icarus" response:
If privacy is a problem, do not use newsequentialid() . You can guess the value of the next generated GUID and therefore access the data associated with this GUID
Change primary key
The column that you want to change is the identifier column, and you specified it as the primary key. Therefore, before deleting an existing ID column, you will need to delete the primary key using another ALTER TABLE SQL command.
See the selected answer from "darnir" for "How do I change the primary key constraint using SQL syntax?"
ALTER TABLE <Table_Name> DROP CONSTRAINT <constraint_name> ALTER TABLE <Table_Name> ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)
See the note “Oleg” to determine if this will be a factor:
PRIMARY KEY CONSTRAINT cannot be changed, you can only drop it and create it again. For large datasets, this can lead to long runtimes and, therefore, inaccessibility of the table.
I had problems when the command with DROP CONSTRAINT above was executed. The results pane indicated a constraint that was automatically generated, although I used a specific constraint name in the ALTER TABLE ... ADD COLUMN command. See the question "Why does SQL continue to create a DF constraint?" and question if you have something similar.
To fix the problem of removing the constraint, I used the answer "ScubaSteve" from this question : "How to remove the default SQL constraint without knowing its name?" With the addition of the Seven note, here are the SQL commands:
DECLARE @ObjectName NVARCHAR(100) SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS WHERE [object_id] = OBJECT_ID('[tableSchema].[tableName]') AND [name] = 'columnName'; IF @ObjectName IS NOT NULL EXEC('ALTER TABLE [tableSchema].[tableName] DROP CONSTRAINT ' + @ObjectName)
Comment "Seven" in the answer by ScubaSteve. I added an if condition as soon as EXEC fails if a restriction is not found.
To make this idmpotent script add IF @ObjectName IS NOT NULL before the EXEC command
Final decision
Be sure to replace MyTableName , MyColumnName and dbo in the code below with the table name, column name (for example, set the column name to Id ) and the table schema, respectively.
public override void Up() { Sql(@" DECLARE @ObjectName NVARCHAR(100) SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS WHERE [object_id] = OBJECT_ID('[dbo].[MyTableName]') AND [name] = 'MyColumnName'; IF @ObjectName IS NOT NULL EXEC('ALTER TABLE [dbo].[MyTableName] DROP CONSTRAINT ' + @ObjectName) ALTER TABLE dbo.MyTableName DROP CONSTRAINT PK_MyTableName, COLUMN MyColumnName ALTER TABLE dbo.MyTableName ADD Id UNIQUEIDENTIFIER DEFAULT (newsequentialid()) NOT NULL CONSTRAINT PK_MyTableName PRIMARY KEY CLUSTERED ([MyColumnName]) "); } public override void Down() { Sql(@" DECLARE @ObjectName NVARCHAR(100) SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS WHERE [object_id] = OBJECT_ID('[dbo].[MyTableName]') AND [name] = 'MyColumnName'; IF @ObjectName IS NOT NULL EXEC('ALTER TABLE [dbo].[MyTableName] DROP CONSTRAINT ' + @ObjectName) ALTER TABLE dbo.MyTableName DROP CONSTRAINT PK_MyTableName, COLUMN Id ALTER TABLE MyTableName ADD MyColumnName int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_MyTableName PRIMARY KEY CLUSTERED ([MyColumnName] ASC) "); }