Today, I worked on a service request that is related with our previous article Lesson Learned #276: ALTER TABLE statement conflicted with the CHECK constraint importing a bacpac - Microsoft Community Hub
But, in this situation, is related about the business logic implemented by our customer. Let's analyze what happened with "The ALTER TABLE statement conflicted with the CHECK constraint "Table1". The conflict occurred in database "DatabaseName", table "Table1"
As we mentioned before, our first approach was to follow up our article: Lesson Learned #276: ALTER TABLE statement conflicted with the CHECK constraint importing a bacpac - Microsoft Community Hub but in this situation, we didn't fix the problem exporting again the data because the referencial integrity among tables are fine.
In this new scenario, we suggested to review the definition of the constraint that is reporting the error message:
create table table2 (id int, [DeletedAt] datetime2 )
create table table1 (id int, name varchar(200) )
INSERT INTO Table2 (id,DeletedAt) values(1,getdate())
INSERT INTO Table2 (id) values(2)
insert into table1 (id) values(1)
insert into table1 (id) values(2)
create function [dbo].[udf_CheckData] (
@ID int
) returns bit as
begin;
declare @r bit = 1;
if not exists (
select 1
from Table2 as r
where r.Id = @ID
and r.[DeletedAt] IS NULL
)
begin
set @r = 0;
end
return @r;
end;
GO
ALTER TABLE [table1] WITH CHECK ADD CONSTRAINT [udf_CheckData_C] CHECK (([dbo].[udf_CheckData]([ID])=(0)))
We identified that the constraint definition is calling a function that is validating data querying another table, when SqlPackage or SSMS import the data, the constraint will validate the data again and if have some rows that didn't match the rule we're going to have the error.
We worked in three steps:
- Import the data:
- Perform a backup from the database source and restore it.
- Disable the constraint that we have the issue.
- Export the data in a bacpac and import it again.
- Analyze why do we have this inconsistency in the database?:
- To analyze the situation, let's try to run the following TSQL: select [dbo].[udf_CheckData]([ID]) as ValidOrNotInTable2, Id as Table1ID from table1 or select Id as Table1ID from table1 where [dbo].[udf_CheckData]([ID]) =1. We found that we have a row that are not matching with the business logic rule (value 1) and for this reason, when we are importing the data in the destination we got the error due to we need to re-evaluate the constraint.
- Why do we have this issue in the source?
- Well, most probably, when the constraint was created in the source, the definition of this was using NOCHECK (without validating the existing rows). Disable check constraints with INSERT and UPDATE statements - SQL Server | Microsoft Learn or column_constraint (Transact-SQL) - SQL Server | Microsoft Learn
In this case, we have two options:
- Fix the data in the source.
- Disable the constraint in the source and enable it when the data will be imported using NOCHECK.
Enjoy!
Microsoft
