Lesson Learned #281: Troubleshooting CHECK constraint issue importing a bacpac due to business logic

Microsoft

Jan 14, 2023

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.