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

▲
20
▼

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?

c#-4.0
entity-framework-5
asked on Stack Overflow Nov 25, 2012 by Peter • edited Nov 26, 2012 by Peter

6 Answers

▲
78
▼

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.

answered on Stack Overflow Feb 4, 2013 by Jordan
▲
5
▼

I also didn't have any problem simply replacing the relevant DropPrimaryKey, DropColumn, AddColumn and AddPrimaryKey commands with a RenameColumn command, e.g.

public partial class RenameKey : DbMigration
{
    public override void Up()
    {    
        RenameColumn("dbo.GameSummary", "OldId", "Id");
    }
}
answered on Stack Overflow Mar 13, 2014 by ADBailey • edited Mar 13, 2014 by TylerDurden
▲
2
▼

I also had a similar problem after my first migrations. What I realized was that after I deleted the database which the first migration created and then too removed the migrations folder created in my mvc application, the problem did not appear again.

answered on Stack Overflow Mar 19, 2016 by Bismark Atta Frimpong
▲
2
▼

first Add-Migration -Name then

public override void Up()
{    
    RenameColumn("dbo.Department", "OldId", "NewId");
}
answered on Stack Overflow Aug 20, 2018 by Milad
▲
1
▼

You can just change the name of the column directly from the class using something like this: 

[Column("ProductID")]

Example: 

namespace Z_Market.Models
{
    public  class Product
    {
        [Key, Column("ProductID")]  //This change the name of the column when you are using migration.  If you have a form created already, you have to change the connection in the for to aim the new column name.  
        public int ID { get; set; }
        public string Description { get; set; }
        public decimal Price { get; set; }
        public DateTime LastBuy { get; set; }
        public float Stock { get; set; }
        public string remarks { get; set; }
        public string deleteme { get; set; }

        public ICollection<SupplierProduct> SupplierProducts { get; set; }
    }
}
answered on Stack Overflow Oct 19, 2017 by Ismael Moreno • edited Oct 19, 2017 by Brandon Minnick
▲
0
▼

The best implementatios was:

/*Replace*/
DropPrimaryKey("dbo.GameSummary", new[] { "OldId" });
DropColumn("dbo.GameSummary", "OldId");
AddColumn("dbo.GameSummary", "Id", c => c.Int(nullable: false, identity: true));
AddPrimaryKey("dbo.GameSummary", "Id");
/*For*/
RenameColumn("dbo.GameSummary", "OldId", "Id");
answered on Stack Overflow Feb 28, 2020 by mariobot

User contributions licensed under CC BY-SA 3.0