Entity Framework 5 Multiple identity columns specified for table. Only one identity column per table is allowed

Question

I am creating this model as part of my code first entity framework

public class NewUserRegistration
{
    [Key]
    public int NewUserRegistrationId { get; set; }    
}

Using the Update-Database -Verbose -Force command in the Package Manger ConsoleI get this exception during the this bit of the update Applying automatic migration: 201211252223088_AutomaticMigration.

ALTER TABLE [dbo].[NewUserRegistration] ADD [NewUserRegistrationId] [int] NOT NULL IDENTITY System.Data.SqlClient.SqlException (0x80131904): Multiple identity columns specified for table 'NewUserRegistration'. Only one identity column per table is allowed. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.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) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable1 operations, Boolean downgrading, Boolean auto) at System.Data.Entity.Migrations.DbMigrator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading) at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable1 pendingMigrations, String targetMigrationId, String lastMigrationId)
at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable1 pendingMigrations, String targetMigrationId, String lastMigrationId)
at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration) at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore() at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run() ClientConnectionId:a39395da-5f2b-48e0-bdac-b48d75a68c68 Multiple identity columns specified for table 'NewUserRegistration'. Only one identity column per table is allowed.

There is plainly only one Identity Column specified. So why is this the case?

When I do this I get no exception.

public class NewUserRegistration
{
    [Key]
    public int Id { get; set; }    
}

Any thoughts on why this is the case?

EDIT

I should say that I am changing the name of the key. The comments say that you can't just do this. How can I drop and recreate?

Is it best to delete the database from SQL and then just run the Update-Database command again?

Answer 1

I encountered the same error when trying to rename a Key column. To make the migration work, I had to reorder the order of operations in my scaffolded migration script.

Here, I made sure to order the Drop operations first, then added the new Key field afterwards.

public partial class RenameKey : DbMigration
{
    public override void Up()
    {
        DropPrimaryKey("dbo.GameSummary", new[] { "OldId" });
        DropColumn("dbo.GameSummary", "OldId");
        AddColumn("dbo.GameSummary", "Id", c => c.Int(nullable: false, identity: true));
        AddPrimaryKey("dbo.GameSummary", "Id");
    }

Hope that helps with your case.