EF Core Update-Database failing when creating Foreign key


When I run Update-Database I get the following error:

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)
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".

If I run the same script it tries to run in the migration in SSMS directly, it works fine. So why is it failing here? The models and context got reverse engineered from an existing database. When I go into SSMS and generate a DROP AND CREATE script for the table that has this foreign key, it comes up with different SQL for that foreign key.

Here is the portion in my DbContext in OnModelCreate()

modelBuilder.Entity<PortPortal>(entity =>
        entity.HasKey(e => e.PortalId)

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

        entity.HasIndex(e => e.DefaultPageId)

        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)
            .HasComment(@"Field: Default Domain");

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

        entity.Property(e => e.FormsAuthentication)
            .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)
            .HasComment(@"Field: Portal Description");

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

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

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

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

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

        entity.HasOne(d => d.Organization)
            .WithMany(p => p.PortPortal)
            .HasForeignKey(d => d.OrganizationId)

        entity.HasOne(d => d.PortPortalPage)
            .WithMany(p => p.PortPortalPortPortalPage)
            .HasForeignKey(d => new { d.DefaultPageId, d.OrganizationId })

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

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

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

        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

And the migration builder code:

    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);

UPDATE: I have updated the code block which has the OnModelCreating(). Is this the wrong place to seed data?

asked on Stack Overflow Jul 27, 2020 by Jason Ayer • edited Jul 28, 2020 by Jason Ayer

1 Answer


It is likely that at least one of your existing rows in your port_portal table already contains a value in one of the two foreign key columns (default_page_id or organization_id), that does not have a corresponding ID on the principal table (port_portal_page).

This would violate the referential integrity of the database and therefore the ALTER TABLE statement fails.

Run the following SQL query to find the invalid references:

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;

UPDATE: I have updated the code block which has the OnModelCreating(). Is this the wrong place to seed data?

No, the place is generally the right one. Take a look Data Seeding, especially the Limitations of model seed data section.

Where do you apply the migration to the database? Manually outside the application as a script (recommended) or as part of the application code (discouraged)?

If all you seed is that one PortPortal entity, than the problem should still be the one that I described above, because you are missing the corresponding entry for the PortPortalPage entity and the referential integrity would again be broken.

You can test that by executing a Sql() call as part of the Up() method with the SQL snippet I suggested above as the base (but inserting the select into a table or something, so you can check it later) and even put a Debugger.Launch() call before it to trigger the JIT debugger and then step through the code.

answered on Stack Overflow Jul 27, 2020 by lauxjpn • edited Jul 28, 2020 by lauxjpn

User contributions licensed under CC BY-SA 3.0