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

c#-4.0entity-framework-5

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?

Best Answer

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.

Related Topic