EF migration : Error changing an indexed field to nullable

Question

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

Using unique index with nullable column is strange. Null is value as any other so only ONE record will be able to have Null value otherwise you will receive unique constraint violation. — Ladislav Mrnka, Apr 03 '13 at 11:56
I though NULL != NULL but don't think I've tried it myself recently - worth checking that. — Mark, Apr 03 '13 at 15:45
I want blank to be a valid option in the combo box. Should I have Null or an empty string for that? — Kirsten, Apr 04 '13 at 23:05

score 10 · Accepted Answer · answered Apr 03 '13 at 15:43

10

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.

answered Apr 03 '13 at 15:43

Mark

880
8
18

Thanks, your answer works. However the SQL alternative fails if the index is not already present. – Kirsten Apr 04 '13 at 01:41
1

Yes you'd need to have an IF EXISTS statement if you wanted that robustness. Personally I'd generate the drop script from SQL management studio and paste that in as that normally includes that check. – Mark Apr 04 '13 at 09:41
i am not seeing the check when I generate from management studio. Am I doing it the right way? right click the index and script index Drop To... I do see a setting in the create script. DROP_EXISTING – Kirsten Apr 04 '13 at 21:04
`IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Sites]') AND name = N'IX_Description') Drop Index [IX_Description] on [dbo].[Sites]` – Kirsten Apr 04 '13 at 21:13
Thanks for this. I wonder why it happened though? I looked through my other migrations and they had auto-generated `DropIndex`es where needed. – Andrew Aug 14 '13 at 16:13
1

Entity Framework auto-generates migrations based on your Model classes, data annotations and Fluent API mappings. During auto-generation, it does not know about anything you have manually added to a migration, so it does not know that a DropIndex is required here. You cannot add the index via the Fluent API either. But if you'd like to be able to you'll find a link to vote for it here: http://stackoverflow.com/a/18694276/150342 (as well as more information about indexes) – Colin Sep 17 '13 at 10:33

EF migration : Error changing an indexed field to nullable

1 Answers1