EF migration : Error changing an indexed field to nullable
entity-framework-5, entity-framework-migrations
Solution
Something like this perhaps?
DropIndex("dbo.Sites", "IX_Description");
AlterColumn("dbo.Sites", "Description", c => c.String(nullable: false, maxLength: 450));
CreateIndex("dbo.Sites", "Description", unique: true);
I think you can also execute SQL direct as below.
Sql("DROP INDEX [IX_Description] ON [dbo].[Sites] WITH ( ONLINE = OFF )");
Which can be useful if you want to add a check the index exists or something.
Problem
The code in my initial migration was as follows ``` CreateTable( "dbo.Sites", c => new { Id = c.Int(nullable: false, identity: true), Description = c.String(maxLength: 450) }) .PrimaryKey(t => t.Id); ``` So that the Description field would be unique I added the following to the end of the UP method CreateIndex("dbo.Sites", "Description", unique: true); Later I decided to make the Description field required. The new migration generates the following change AlterColumn("dbo.Sites", "Description", c => c.String(nullable: false, maxLength: 450)); However when this change attempts to run I get an error ALTER TABLE ALTER COLUMN Description failed because one or more objects access this column. I was able to isolate the SQL line using the profiler, as ALTER TABLE [dbo].[Sites] ALTER COLUMN [Description] nvarchar NOT NULL Which gives me the following error when I run it in Management Studio Msg 5074, Level 16, State 1, Line 1 The index 'IX_Description' is dependent on column 'Description'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN Description failed because one or more objects access this column. How can I get the migration code to drop the index, then change alter the column and then re-build the index ? I am using SQL Server 2008 R2