Lesson Learned #276: ALTER TABLE statement conflicted with the CHECK constraint importing a bacpac
Published Jan 11 2023 05:22 PM 2,401 Views

Today, I worked on a service request where our customer faced the following error: The ALTER TABLE statement conflicted with the CHECK constraint "Table1". The conflict occurred in database "DatabaseName", table "Table2".

 

Full error message:

Microsoft.Data.Tools.Diagnostics.Tracer Verbose: 0 : 2023-01-11T11:11:35 : Executing Step 341, Not Tracked, Type 'EnableConstraintsStep', Section 'None', Operation '0', Ignorable Errors 'None', Script is as follows: PRINT N'Checking constraint: table1_table2_updatedby_foreign [dbo].[Table1]'; ALTER TABLE [dbo].[Table1] WITH CHECK CHECK CONSTRAINT [table1_table2_updatedby_foreign];

Microsoft.Data.Tools.Diagnostics.Tracer Error: 19 : 2023-01-11T11:11:39 : Retry requested: Retry count = 1. Delay = 00:00:00.2500000, SQL Error Code = -2146232060, SQL Error Number = 547, Can retry error = True, Will retry = True Microsoft.Data.SqlClient.SqlException (0x80131904): The ALTER TABLE statement conflicted with the CHECK constraint "Table1". The conflict occurred in database "DatabaseName", table "Table2". Error Number:547,State:0,Class:16

 

This situation happened at the moment that the process has inserted all the rows in all tables and needs to enable the constrains/indexes. Points that a foreign key doesn't exist in the related table. You could see more details here "For an export to be transactionally consistent, you must ensure either that no write activity is occurring during the export, or that you are exporting from a transactionally consistent copy of your database." based on this link 

 

The solution is to export the data again, avoiding any writing operation in the database during this process. After it, initiate again the import process.

 

Enjoy!

Version history
Last update:
‎Jan 11 2023 05:24 PM
Updated by: