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 -...
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 -... 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:
In this case, we have two options:
Enjoy!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.