locked
Migration creation successful / update-database unsuccessful RRS feed

  • Question

  • User2074931137 posted

    I created a migration (PM> add-migration ScheduleCalendarDate1 below)

    Tried to update database (PM> update-database)

    Got this error:
    Error Number:547,State:0,Class:16
    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Schedule_dbo.CalendarDate_CalendarDate1". The conflict occurred in database "fvm", table "dbo.CalendarDate", column 'CalendarDateID'.

    When I tried to create a migration to reverse the migration I got the following error:
    PM> add-migration ScheduleCalendarDate1RB
    Unable to generate an explicit migration because the following explicit migrations are pending: [201901281242341_ScheduleCalendarDate1]. Apply the pending explicit migrations before attempting to generate a new explicit migration.

    Question:
    Since the migrations was created successfully, but the database was not successfully updated - how do I go back to before the migration that created the error? (can I simply delete migration folders under migrations?)

    PackageManager Console
    PM> add-migration ScheduleCalendarDate1
    Scaffolding migration 'ScheduleCalendarDate1'.
    The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running 'Add-Migration ScheduleCalendarDate1' again.
    PM> update-database
    Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
    Applying explicit migrations: [201901281242341_ScheduleCalendarDate1].
    Applying explicit migration: 201901281242341_ScheduleCalendarDate1.
    System.Data.SqlClient.SqlException (0x80131904): The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Schedule_dbo.CalendarDate_CalendarDate1". The conflict occurred in database "fvm", table "dbo.CalendarDate", column 'CalendarDateID'.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)
    at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
    at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
    at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
    at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
    at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
    at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
    at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinTransaction(IEnumerable`1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext)
    at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinNewTransaction(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext)
    at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext)
    at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection)
    at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClass32.<ExecuteStatements>b__30()
    at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0()
    at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
    at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation)
    at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements, DbTransaction existingTransaction)
    at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)
    at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)
    at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, VersionedModel targetModel, IEnumerable`1 operations, IEnumerable`1 systemOperations, Boolean downgrading, Boolean auto)
    at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
    at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
    at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
    at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
    at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration)
    at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClasse.<Update>b__d()
    at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
    at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
    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:acd3b47a-eacd-4a84-856d-3dd33b5bc865
    Error Number:547,State:0,Class:16
    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Schedule_dbo.CalendarDate_CalendarDate1". The conflict occurred in database "fvm", table "dbo.CalendarDate", column 'CalendarDateID'.

    Monday, January 28, 2019 1:59 PM

Answers

  • User1520731567 posted

    Hi 3v3rhart,

    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Schedule_dbo.CalendarDate_CalendarDate1". The conflict occurred in database "fvm", table "dbo.CalendarDate", column 'CalendarDateID'

    Check that there is not existing data in the database that is conflicting with the FK constraint causing the creation to fail.

    FOREIGN KEY should be primary key in the other table.

    If you still have problems,please your models.

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 29, 2019 5:19 AM