How to change datatype length of a column using code first? #4269

manojreddy created 7 years ago

I have a table which has a column as nvarchar(255), I want to change the length to nvarchar(250), But When I run update-database I get the following error.

Each package is licensed to you by its owner. NuGet is not responsible for, nor does it grant any licenses to, third-party packages. Some packages may include dependencies which are governed by additional licenses. Follow the package source (feed) URL to determine any dependencies.

Package Manager Console Host Version 4.3.0.4339

Type 'get-help NuGet' to see all available NuGet commands.

PM> Update-Database Executed DbCommand (56ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT OBJECT_ID(N'__EFMigrationsHistory'); Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT OBJECT_ID(N'__EFMigrationsHistory'); Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [MigrationId], [ProductVersion] FROM [__EFMigrationsHistory] ORDER BY [MigrationId]; Applying migration '20171201072435_Updating DataTypes for Currencies based on SBO'. Executed DbCommand (108ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] DECLARE @var0 sysname; SELECT @var0 = [d].[name] FROM [sys].[default_constraints] [d] INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id] WHERE ([d].[parent_object_id] = OBJECT_ID(N'CurrencyDetails') AND [c].[name] = N'NoOfDecimal'); IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [CurrencyDetails] DROP CONSTRAINT [' + @var0 + '];'); ALTER TABLE [CurrencyDetails] ALTER COLUMN [NoOfDecimal] tinyint; Executed DbCommand (21ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] DECLARE @var1 sysname; SELECT @var1 = [d].[name] FROM [sys].[default_constraints] [d] INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id] WHERE ([d].[parent_object_id] = OBJECT_ID(N'CurrencyDetails') AND [c].[name] = N'CurrencySymbol'); IF @var1 IS NOT NULL EXEC(N'ALTER TABLE [CurrencyDetails] DROP CONSTRAINT [' + @var1 + '];'); ALTER TABLE [CurrencyDetails] ALTER COLUMN [CurrencySymbol] nvarchar(3); Executed DbCommand (45ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] DECLARE @var2 sysname; SELECT @var2 = [d].[name] FROM [sys].[default_constraints] [d] INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id] WHERE ([d].[parent_object_id] = OBJECT_ID(N'CurrencyDetails') AND [c].[name] = N'CurrencyDesc'); IF @var2 IS NOT NULL EXEC(N'ALTER TABLE [CurrencyDetails] DROP CONSTRAINT [' + @var2 + '];'); ALTER TABLE [CurrencyDetails] ALTER COLUMN [CurrencyDesc] nvarchar(40) NOT NULL; Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20171201072435_Updating DataTypes for Currencies based on SBO', N'1.1.2'); Done. PM> Update-Database Executed DbCommand (214ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT OBJECT_ID(N'__EFMigrationsHistory'); Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT OBJECT_ID(N'__EFMigrationsHistory'); Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [MigrationId], [ProductVersion] FROM [__EFMigrationsHistory] ORDER BY [MigrationId]; Applying migration '20171201104545_Changing Defination for Test based on SBO'. Executed DbCommand (3,345ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] DECLARE @var0 sysname; SELECT @var0 = [d].[name] FROM [sys].[default_constraints] [d] INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id] WHERE ([d].[parent_object_id] = OBJECT_ID(N'Test') AND [c].[name] = N'DescShort'); IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Test] DROP CONSTRAINT [' + @var0 + '];'); ALTER TABLE [Test] ALTER COLUMN [DescShort] nvarchar(40) NOT NULL; Executed DbCommand (1,356ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] DECLARE @var1 sysname; SELECT @var1 = [d].[name] FROM [sys].[default_constraints] [d] INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id] WHERE ([d].[parent_object_id] = OBJECT_ID(N'Test') AND [c].[name] = N'DescLong'); IF @var1 IS NOT NULL EXEC(N'ALTER TABLE [Test] DROP CONSTRAINT [' + @var1 + '];'); ALTER TABLE [Test] ALTER COLUMN [DescLong] nvarchar(40) NOT NULL; Executed DbCommand (269ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] DECLARE @var2 sysname; SELECT @var2 = [d].[name] FROM [sys].[default_constraints] [d] INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id] WHERE ([d].[parent_object_id] = OBJECT_ID(N'Test') AND [c].[name] = N'CurrencyCode'); IF @var2 IS NOT NULL EXEC(N'ALTER TABLE [Test] DROP CONSTRAINT [' + @var2 + '];'); ALTER TABLE [Test] ALTER COLUMN [CurrencyCode] nvarchar(3); Executed DbCommand (261ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] DECLARE @var3 sysname; SELECT @var3 = [d].[name] FROM [sys].[default_constraints] [d] INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id] WHERE ([d].[parent_object_id] = OBJECT_ID(N'Test') AND [c].[name] = N'TestParent'); IF @var3 IS NOT NULL EXEC(N'ALTER TABLE [Test] DROP CONSTRAINT [' + @var3 + '];'); ALTER TABLE [Test] ALTER COLUMN [TestParent] nvarchar(255); Executed DbCommand (72ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20171201104545_Changing Defination for Test based on SBO', N'1.1.2'); Applying migration '20171201123035_Checking Unique Key Constrains'. Executed DbCommand (401ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] DROP INDEX [IX_Article_ArticleName_IsDeleted] ON [Article]; Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20171201123035_Checking Unique Key Constrains', N'1.1.2'); Applying migration '20171201123219_Adding back Unique Constrains'. Executed DbCommand (1,021ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE UNIQUE INDEX [IX_Article_ArticleName_IsDeleted] ON [Article] ([ArticleName], [IsDeleted]); Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20171201123219_Adding back Unique Constrains', N'1.1.2'); Applying migration '20171201124913_Changing coloumn name for PromoPackResultsArticles'. Executed DbCommand (689ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] ALTER TABLE [PromoPackResultsArticles] DROP CONSTRAINT [FK_PromoPackResultsArticles_Article_ArticleId]; Executed DbCommand (195ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] DECLARE @var4 sysname; SELECT @var4 = [d].[name] FROM [sys].[default_constraints] [d] INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id] WHERE ([d].[parent_object_id] = OBJECT_ID(N'PromoPackResultsArticles') AND [c].[name] = N'PromoPackArticleId'); IF @var4 IS NOT NULL EXEC(N'ALTER TABLE [PromoPackResultsArticles] DROP CONSTRAINT [' + @var4 + '];'); ALTER TABLE [PromoPackResultsArticles] DROP COLUMN [PromoPackArticleId]; Executed DbCommand (52ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] DROP INDEX [IX_PromoPackResultsArticles_ArticleId] ON [PromoPackResultsArticles]; DECLARE @var5 sysname; SELECT @var5 = [d].[name] FROM [sys].[default_constraints] [d] INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id] WHERE ([d].[parent_object_id] = OBJECT_ID(N'PromoPackResultsArticles') AND [c].[name] = N'ArticleId'); IF @var5 IS NOT NULL EXEC(N'ALTER TABLE [PromoPackResultsArticles] DROP CONSTRAINT [' + @var5 + '];'); ALTER TABLE [PromoPackResultsArticles] ALTER COLUMN [ArticleId] int NOT NULL; CREATE INDEX [IX_PromoPackResultsArticles_ArticleId] ON [PromoPackResultsArticles] ([ArticleId]); Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] ALTER TABLE [PromoPackResultsArticles] ADD CONSTRAINT [FK_PromoPackResultsArticles_Article_ArticleId] FOREIGN KEY ([ArticleId]) REFERENCES [Article] ([Id]); Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20171201124913_Changing coloumn name for PromoPackResultsArticles', N'1.1.2'); Done. PM> Add-Migration cmdlet Add-Migration at command pipeline position 1 Supply values for the following parameters: Name: ChangeDatatypeLengthto250 An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy. To undo this action, use Remove-Migration. PM> Update-Database Executed DbCommand (205ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT OBJECT_ID(N'__EFMigrationsHistory'); Executed DbCommand (116ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT OBJECT_ID(N'__EFMigrationsHistory'); Executed DbCommand (136ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [MigrationId], [ProductVersion] FROM [__EFMigrationsHistory] ORDER BY [MigrationId]; Applying migration '20171203065419_ChangeDatatypeLengthto250'. Executed DbCommand (223ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] DECLARE @var0 sysname; SELECT @var0 = [d].[name] FROM [sys].[default_constraints] [d] INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id] WHERE ([d].[parent_object_id] = OBJECT_ID(N'Test') AND [c].[name] = N'TestParent'); IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Test] DROP CONSTRAINT [' + @var0 + '];'); ALTER TABLE [Test] ALTER COLUMN [TestParent] nvarchar(250); Executed DbCommand (171ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] DECLARE @var1 sysname; SELECT @var1 = [d].[name] FROM [sys].[default_constraints] [d] INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id] WHERE ([d].[parent_object_id] = OBJECT_ID(N'Test') AND [c].[name] = N'TestCode'); IF @var1 IS NOT NULL EXEC(N'ALTER TABLE [Test] DROP CONSTRAINT [' + @var1 + '];'); ALTER TABLE [Test] ALTER COLUMN [TestCode] nvarchar(250) NOT NULL; System.Data.SqlClient.SqlException: The object 'IX_TestCode' is dependent on column 'TestCode'. ALTER TABLE ALTER COLUMN TestCode failed because one or more objects access this column. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary2 parameterValues, Boolean closeConnection) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration) at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action) ClientConnectionId:550138fa-3c27-4b75-86b8-55485492bcf9 Error Number:5074,State:1,Class:16 The object 'IX_TestCode' is dependent on column 'TestCode'. PM>

In old migration I have a file.

migrationBuilder.CreateTable(
                name: "Test",
                columns: table => new
                {
                    Id = table.Column&lt;int&gt;(nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                    TestCode = table.Column&lt;string&gt;(maxLength: 255, nullable: false),
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Test", x => x.Id);
                    table.UniqueConstraint("IX_TestCode", x => x.TestCode);
                });

New Migration class.

public partial class ChangeDatatypeLengthto250 : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AlterColumn&lt;string&gt;(
                name: "TestParent",
                table: "Test",
                maxLength: 250,
                nullable: true,
                oldClrType: typeof(string),
                oldMaxLength: 255,
                oldNullable: true);

            migrationBuilder.AlterColumn&lt;string&gt;(
                name: "TestCode",
                table: "Test",
                maxLength: 250,
                nullable: false,
                oldClrType: typeof(string),
                oldMaxLength: 255);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AlterColumn&lt;string&gt;(
                name: "TestParent",
                table: "Test",
                maxLength: 255,
                nullable: true,
                oldClrType: typeof(string),
                oldMaxLength: 250,
                oldNullable: true);

            migrationBuilder.AlterColumn&lt;string&gt;(
                name: "TestCode",
                table: "Test",
                maxLength: 255,
                nullable: false,
                oldClrType: typeof(string),
                oldMaxLength: 250);
        }
    }

1 Answer(s)