Handling duplicates when adding unique index to table with existing rows in code first migration

Question

I want to add a new column with a unique index to an existing table using a code first migration. Because the table already has data, all existing rows are set to NULL for the new column when it's created. The migration fails to apply because this violates the unique constraint of the new index.

Here is my migration:

public override void Up()
{
    AddColumn("dbo.SignInToken", "Token", c => c.String(maxLength: 32));
    // How can I update existing rows here?
    CreateIndex("dbo.SignInToken", "Token", unique: true);
}

And the error:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.SignInToken' and the index name 'IX_Token'. The duplicate key value is ().

The new column will contain a randomly generated string which I set in code.

I know that I can use the Sql method to execute raw SQL within the migration. I thought of using a cursor to update each row, but then I would have to write a stored procedure to mimic the C# method which generates the random token. I would prefer not to have to do this. Another thing I thought of was that if there were a way to retrieve all of the rows, I could loop through them and execute the update statements on each row using the Sql method, but I don't know if this is possible.

Is there a way to update all existing rows individually from within the migration before adding the unique constraint?

Sam · Accepted Answer · 2016-03-21 19:16:17Z

4

One solution is to copy the primary key into the new column. This can then be updated if necessary in the seed method.

Updated migration:

public override void Up()
{
    AddColumn("dbo.SignInToken", "Token", c => c.String(maxLength: 40));
    Sql("UPDATE dbo.SignInToken SET Token = Id");
    CreateIndex("dbo.SignInToken", "Token", unique: true);
}

Then you can use the DbContext from the Seed method to update the rows if necessary. Some logic would be required to make sure it doesn't overwrite new data in future migrations (e.g. only update the row if Id == Token).

edited Mar 21, 2016 at 19:16

answered Mar 21, 2016 at 18:57

Sam

5,0144 gold badges32 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Over a year ago

This solution worked well for me. I actually didn't need to bother updating the data in my Seed method, but included that information for others.

Hossein Narimani Rad · Accepted Answer · 2017-05-20 06:18:01Z

0

Also in the case you didn't have a already existing unique column (to copy it's values) you and want to add a unique column to a table that have already some rows you can try this approach

AddColumn("dbo.SignInToken", 
          "Token", 
          c => c.Int(nullable: false,
                     defaultValueSql: "CONVERT(int, CONVERT(VARBINARY(16), NEWID(), 1))"));

CreateIndex("dbo.IriSqlServerLayer", "Order", unique: true, name: "IX_UniqueOrder");

answered May 20, 2017 at 6:18

Hossein Narimani Rad

32.7k19 gold badges92 silver badges121 bronze badges

Collectives™ on Stack Overflow

Handling duplicates when adding unique index to table with existing rows in code first migration

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related