CODEWITHSUNDEEP
sql-serverasp.net-coreentity-framework-coreazure-sql-database
RashmiMs
RashmiMs

Reputation: 189

Can not alter column having Unique Constraint with Index using EF Core

I have a table called 'JobVendor' which has column called 'shortName'. This is part of Unique key constraint with Index on it.

        jobVendorBuilder.HasIndex(e => new { e.JobFk, e.ShortName }, "UQ_JobVendor_Job_shortName")
            .IsUnique();

I am trying to alter shortName length from 32 to 16 character using EF core fluent API.

        jobVendorBuilder.Property(e => e.ShortName)
            .IsRequired()
            .HasMaxLength(16)
            .IsUnicode(false);

Add-Migration creates below code

migrationBuilder.AlterColumn<string>(
            name: "ShortName",
            table: "JobVendor",
            type: "varchar(16)",
            unicode: false,
            maxLength: 16,
            nullable: false,
            oldClrType: typeof(string),
            oldType: "varchar(32)",
            oldUnicode: false,
            oldMaxLength: 32);

But when tried to update Database, it throws below error...

An explicit DROP INDEX is not allowed on index 'JobVendor.UQ_JobVendor_Job_shortName'. It is being used for UNIQUE KEY constraint enforcement.

The SQL query that executed in the background...

DROP INDEX [UQ_JobVendor_Job_shortName] ON [JobVendor];
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'[JobVendor]') AND [c].[name] = N'ShortName');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [JobVendor] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [JobVendor] ALTER COLUMN [ShortName] varchar(16) NOT NULL;
CREATE UNIQUE INDEX [UQ_JobVendor_Job_shortName] ON [JobVendor] ([Job_FK], [ShortName]);

I am not sure why it is trying to drop index while altering column...

I have gone through couple of stack overflow questions and they suggested to drop index first and add later so I tried removing the Unique constraint and added migration. It added drop Index to migration explicitly

migrationBuilder.DropIndex(
            name: "UQ_JobVendor_Job_shortName",
            table: "JobVendor");

        migrationBuilder.AlterColumn<string>(
            name: "ShortName",
            table: "JobVendor",
            type: "varchar(16)",
            unicode: false,
            maxLength: 16,
            nullable: false,
            oldClrType: typeof(string),
            oldType: "varchar(32)",
            oldUnicode: false,
            oldMaxLength: 32);

But trying to Update-Database still throws same error, as in this case too it is trying to drop index, which it is not able to do so...

Failed executing DbCommand (344ms) [Parameters=[], CommandType='Text', 
            CommandTimeout='30']

Internal SQL statement executed

    DROP INDEX [UQ_JobVendor_Job_shortName] ON [JobVendor];

Do I need to remove the column and add back to resolve this ? or any other better solution?

Upvotes: 0

Views: 1414

Answers (1)

Sean Pearce
Sean Pearce

Reputation: 1169

migrationBuilder.DropUniqueConstraint(
        name: "UQ_JobVendor_Job_shortName",
        table: "JobVendor");

    migrationBuilder.AlterColumn<string>(
        name: "ShortName",
        table: "JobVendor",
        type: "varchar(16)",
        unicode: false,
        maxLength: 16,
        nullable: false,
        oldClrType: typeof(string),
        oldType: "varchar(32)",
        oldUnicode: false,
        oldMaxLength: 32);

Upvotes: 1

Related Questions