Update-Database fails on foreign key conflict

0

I've an application which uses Entity Framework. It contains several migration files which do various database operations. Another user has been making updates directly to the database and now I can't get one of my migrations to apply when I run Update-Database. This is the error I get:

PM> Update-Database Build started... Build succeeded. Failed executing DbCommand (56ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] ALTER TABLE [Balance1Part] ADD CONSTRAINT [FK_Balance1Part_Balance1_Balance1Id] FOREIGN KEY ([Balance1Id]) REFERENCES [Balance1] ([Id]) ON DELETE CASCADE; fail: Microsoft.EntityFrameworkCore.Database.Command[20102] Failed executing DbCommand (56ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] ALTER TABLE [Balance1Part] ADD CONSTRAINT [FK_Balance1Part_Balance1_Balance1Id] FOREIGN KEY ([Balance1Id]) REFERENCES [Balance1] ([Id]) ON DELETE CASCADE; Microsoft.Data.SqlClient.SqlException (0x80131904): The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Balance1Part_Balance1_Balance1Id". The conflict occurred in database "db-vrhit05042021", table "dbo.Balance1", column 'Id'. at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite) at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName) at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration) at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String contextType) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action) ClientConnectionId:b538f2ff-0a95-4975-ab15-ed75eca72382 Error Number:547,State:0,Class:16 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Balance1Part_Balance1_Balance1Id". The conflict occurred in database "db-vrhit05042021", table "dbo.Balance1", column 'Id'.

The migration I'm looking to apply here should add the foreign key FK_Balance1Part_Balance1_Balance1Id and that foreign key doesn't seem to exist on the database. How can I resolve this issue?

I'm trying to do this on an Azure SQL Server instance.

Here's the migration:

public partial class AddIdcolumns : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<int>(
            name: "CalibrationId",
            table: "Symptoms",
            nullable: false,
            defaultValue: 0);

        migrationBuilder.AddColumn<int>(
            name: "Id",
            table: "Balance1",
            nullable: false,
            defaultValue: 0)
            .Annotation("SqlServer:Identity", "1, 1");

        migrationBuilder.AddPrimaryKey(
            name: "PK_Balance1",
            table: "Balance1",
            column: "Id");

        migrationBuilder.CreateIndex(
            name: "IX_Symptoms_CalibrationId",
            table: "Symptoms",
            column: "CalibrationId");

        migrationBuilder.CreateIndex(
            name: "IX_Balance1Part_Balance1Id",
            table: "Balance1Part",
            column: "Balance1Id");

        migrationBuilder.AddForeignKey(
            name: "FK_Balance1Part_Balance1_Balance1Id",
            table: "Balance1Part",
            column: "Balance1Id",
            principalTable: "Balance1",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);

        migrationBuilder.AddForeignKey(
            name: "FK_Symptoms_Calibration_CalibrationId",
            table: "Symptoms",
            column: "CalibrationId",
            principalTable: "Calibration",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropForeignKey(
            name: "FK_Balance1Part_Balance1_Balance1Id",
            table: "Balance1Part");

        migrationBuilder.DropForeignKey(
            name: "FK_Symptoms_Calibration_CalibrationId",
            table: "Symptoms");

        migrationBuilder.DropIndex(
            name: "IX_Symptoms_CalibrationId",
            table: "Symptoms");

        migrationBuilder.DropIndex(
            name: "IX_Balance1Part_Balance1Id",
            table: "Balance1Part");

        migrationBuilder.DropPrimaryKey(
            name: "PK_Balance1",
            table: "Balance1");

        migrationBuilder.DropColumn(
            name: "CalibrationId",
            table: "Symptoms");

        migrationBuilder.DropColumn(
            name: "Id",
            table: "Balance1");
    }
}

Context:

public class VRHitContext : DbContext
{
    public VRHitContext(DbContextOptions<VRHitContext> options)
        : base(options)
    {

    }

    public DbSet<Organisation> Organisations { get; set; }
    public DbSet<ClientDeviceRegistration> ClientDeviceRegistrations { get; set; }

    public DbSet<Session> Sessions { get; set; }
    public DbSet<User> Users { get; set; }

    public DbSet<DataFile> DataFiles { get; set; }

    public DbSet<Symptoms> Symptoms { get; set; }
    public DbSet<Balance1> Balance1 { get; set; }
    public DbSet<Balance1Raw> Balance1Raw { get; set; }
    public DbSet<Balance2> Balance2 { get; set; }
    public DbSet<Balance2Raw> Balance2Raw { get; set; }
    public DbSet<Calibration> Calibration { get; set; }
    public DbSet<CogMotor1> CogMotor1 { get; set; }
    public DbSet<CogMotor1Raw> CogMotor1Raw { get; set; }
    public DbSet<CogMotor2> CogMotor2 { get; set; }
    public DbSet<CogMotor2Raw> CogMotor2Raw { get; set; }

    public DbSet<CogMotor3> CogMotor3 { get; set; }
    public DbSet<CogMotor3Raw> CogMotor3Raw { get; set; }

    public DbSet<Gait> Gait { get; set; }
    public DbSet<GaitRaw> GaitRaw { get; set; }

    public DbSet<Interception1> Interception1 { get; set; }
    public DbSet<Interception2> Interception2 { get; set; }
    public DbSet<InterceptionPlayerRaw> InterceptionPlayerRaw { get; set; }
    public DbSet<ManualDexterity> ManualDexterity { get; set; }
    public DbSet<ManualDexterityRaw> ManualDexterityRaw { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>().HasIndex(u => u.UserIdentifier).IsUnique();
        modelBuilder.Entity<Balance1Part>()
            .HasKey(o => new { o.Id, o.Balance1Id });

        // Set default decimal precision.
        foreach (var property in modelBuilder.Model.GetEntityTypes()
            .SelectMany(t => t.GetProperties())
            .Where(p => p.ClrType == typeof(decimal) || p.ClrType == typeof(decimal?)))
                    {
                        property.SetColumnType("decimal(38, 10)");
                    }


        // Set default for GUID primary keys.
        foreach (var property in modelBuilder.Model.GetEntityTypes()
            .SelectMany(t => t.GetProperties())
            .Where(p => (p.ClrType == typeof(Guid) || p.ClrType == typeof(Guid?)) && p.IsPrimaryKey()))
        {
            property.SetDefaultValueSql("NEWID()");
        }
    }
}
c#
.net
entity-framework-core
asked on Stack Overflow May 10, 2021 by runnerpaul • edited May 10, 2021 by runnerpaul

1 Answer

1

I would say that this error occurred because you tried to add a foreign key from Balance1Part.Balance1Id to Balance1.Id but some values in Balance1Part.Balance1Id don't match any in Balance1.Id. It should be impossible to create a relation that violates referential integrity. Try checking for missing keys in Balance1Part:

SELECT Balance1Id FROM Balance1Part
WHERE Balance1Id NOT IN
(SELECT Id from Balance1)

To resolve add migrationBuilder.Sql("DELETE FROM [Balance1Part]", true); to the migration.

answered on Stack Overflow May 10, 2021 by Guru Stron • edited May 10, 2021 by runnerpaul

User contributions licensed under CC BY-SA 3.0