时间戳

首页 > 解决方案 > 创建外键时EF Core更新数据库失败

entity-framework-core - 创建外键时EF Core更新数据库失败

问题描述

当我运行时，Update-Database我收到以下错误：

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
   Failed executing DbCommand (938ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
   ALTER TABLE [port_portal] ADD CONSTRAINT [FK_port_portal_port_portal_page_default] FOREIGN KEY ([default_page_id], [organization_id]) REFERENCES [port_portal_page] ([page_id], [organization_id]) ON DELETE NO ACTION;
Failed executing DbCommand (938ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE [port_portal] ADD CONSTRAINT [FK_port_portal_port_portal_page_default] FOREIGN KEY ([default_page_id], [organization_id]) REFERENCES [port_portal_page] ([page_id], [organization_id]) ON DELETE NO ACTION;
Microsoft.Data.SqlClient.SqlException (0x80131904): The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_port_portal_port_portal_page_default". The conflict occurred in database "ArenaSprint", table "dbo.port_portal_page".
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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(TaskCompletionSource`1 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, IReadOnlyDictionary`2 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:8a9a2e38-2f14-4185-818f-919fe99bc5fb
Error Number:547,State:0,Class:16
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_port_portal_port_portal_page_default". The conflict occurred in database "ArenaSprint", table "dbo.port_portal_page".

如果我运行相同的脚本，它会尝试直接在 SSMS 中的迁移中运行，它工作正常。那么为什么在这里失败呢？模型和上下文是从现有数据库进行逆向工程的。当我进入 SSMS 并DROP AND CREATE为具有此外键的表生成脚本时，它会为该外键提供不同的 SQL。

这是我的 DbContext 中的部分OnModelCreate()

modelBuilder.Entity<PortPortal>(entity =>
    {
        entity.HasKey(e => e.PortalId)
            .HasName("PK_orgn_portal");

        entity.HasComment(@"Contains Portal inforamation dministration > Portal List");

        entity.HasIndex(e => e.DefaultPageId)
            .HasName("IX_port_portal");

        entity.Property(e => e.CreatedBy).IsUnicode(false);

        entity.Property(e => e.DateCreated).HasDefaultValueSql("(getdate())");

        entity.Property(e => e.DateModified).HasDefaultValueSql("(getdate())");

        entity.Property(e => e.DefaultDomain)
            .IsUnicode(false)
            .HasDefaultValueSql("('')")
            .HasComment(@"Field: Default Domain");

        entity.Property(e => e.DefaultPageId).HasComment(@"Field: Default Page ID");

        entity.Property(e => e.FormsAuthentication)
            .HasDefaultValueSql("((1))")
            .HasComment(@"Field: Authentication  ");

        entity.Property(e => e.JqueryInclude).IsUnicode(false);

        entity.Property(e => e.LoginPageId).HasComment(@"Field: Login Page ID");

        entity.Property(e => e.ModifiedBy).IsUnicode(false);

        entity.Property(e => e.NavigationStyleSheet).IsUnicode(false);

        entity.Property(e => e.PortalDesc)
            .IsUnicode(false)
            .HasComment(@"Field: Portal Description");

        entity.Property(e => e.PortalName)
            .IsUnicode(false)
            .HasComment(@"Field: Portal Name");

        entity.Property(e => e.PortalNotes)
            .IsUnicode(false)
            .HasComment(@"Field: Portal Notes");

        entity.Property(e => e.PortalStyleSheet)
            .IsUnicode(false)
            .HasDefaultValueSql("('')")
            .HasComment(@"Field: Portal Style Sheet");

        entity.Property(e => e.PortalTitle)
            .IsUnicode(false)
            .HasDefaultValueSql("('')")
            .HasComment(@"Field: Portal Title");

        entity.Property(e => e.TreeStyleSheet).IsUnicode(false);

        entity.HasOne(d => d.Organization)
            .WithMany(p => p.PortPortal)
            .HasForeignKey(d => d.OrganizationId)
            .OnDelete(DeleteBehavior.ClientSetNull)
            .HasConstraintName("FK_port_portal_orgn_organization");

        entity.HasOne(d => d.PortPortalPage)
            .WithMany(p => p.PortPortalPortPortalPage)
            .HasForeignKey(d => new { d.DefaultPageId, d.OrganizationId })
            .OnDelete(DeleteBehavior.ClientSetNull)
            .HasConstraintName("FK_port_portal_port_portal_page_default");

        entity.HasOne(d => d.PortPortalPageNavigation)
            .WithMany(p => p.PortPortalPortPortalPageNavigation)
            .HasForeignKey(d => new { d.LoginPageId, d.OrganizationId })
            .HasConstraintName("FK_port_portal_port_portal_page_login");

        entity.HasOne(d => d.PortPortalPage1)
            .WithMany(p => p.PortPortalPortPortalPage1)
            .HasForeignKey(d => new { d.PageNotFoundId, d.OrganizationId })
            .HasConstraintName("FK_port_portal_port_portal_page_not_found");

        entity.HasOne(d => d.PortPortalPage2)
            .WithMany(p => p.PortPortalPortPortalPage2)
            .HasForeignKey(d => new { d.PersonPopupPageId, d.OrganizationId })
            .HasConstraintName("FK_port_portal_port_portal_page_popup");

        entity.HasData(new PortPortal
            {
                PortalId = 1,
                CreatedBy = "Setup",
                ModifiedBy = "Setup",
                OrganizationId = 1,
                PortalName = "Staff Web",
                PortalTitle = "My Church Staff Portal",
                PortalDesc = "",
                PortalNotes = "",
                PortalDefLastUpdated = null,
                PortalStyleSheet = "",
                LoginPageId = null,
                DefaultDomain = "",
                FormsAuthentication = true,
                IsMobile = false,
                PageNotFoundId = null,
                PersonPopupPageId = null,
                TreeStyleSheet = null,
                NavigationStyleSheet = null,
                JqueryInclude = null,
                PortalEulaPrompt = true,
                BootstrapCssInclude = null,
                BootstrapJsInclude = null
            });
    });

以及迁移构建器代码：

migrationBuilder.AddForeignKey(
    name: "FK_port_portal_port_portal_page_default",
    table: "port_portal",
    columns: new[] { "default_page_id", "organization_id" },
    principalTable: "port_portal_page",
    principalColumns: new[] { "page_id", "organization_id" },
    onDelete: ReferentialAction.Restrict);

更新：我已经更新了具有OnModelCreating(). 这是播种数据的错误位置吗？

标签： entity-framework-core

解决方案

很可能您的表中至少有一个现有行port_portal已经包含两个外键列（default_page_id或）之一中的值，该值在主体表（ ）organization_id上没有对应的 ID 。port_portal_page

这将违反数据库的引用完整性，因此ALTER TABLE语句失败。

运行以下 SQL 查询以查找无效引用：

select *
from port_portal as pp
left join port_portal_page as ppp on ppp.default_page_id = pp.page_id AND ppp.organization_id = pp.organization_id
where ppp.default_page_id is null;

更新：我已经更新了具有 OnModelCreating() 的代码块。这是播种数据的错误位置吗？

不，这个地方通常是正确的。看看数据播种，尤其是模型种子数据的限制部分。

您在哪里将迁移应用到数据库？在应用程序外部手动作为脚本（推荐）或作为应用程序代码的一部分（不鼓励）？

如果您播种的只是一个PortPortal实体，那么问题应该仍然是我上面描述的那个，因为您缺少PortPortalPage实体的相应条目，并且参照完整性将再次被破坏。

您可以通过使用我上面建议的 SQL 片段作为基础的方法的一部分执行Sql()调用来测试它Up()（但将选择插入到表或其他东西中，以便稍后检查），甚至Debugger.Launch()在它之前调用以触发JIT 调试器，然后单步执行代码。

推荐阅读