Конфликт инструкции alter table с ограничением check

alter FUNCTION [Kuri].[fnGetAge](@kuri_cust_Id int,@amt decimal)
RETURNS SMALLINT
AS
    BEGIN
    DECLARE @isVallid  bit = 0
    declare @payed decimal(14,2)
    declare @totaltillnow decimal(14,2)
    select @payed = isnull(SUM(Payment.amt),0)  from Kuri.Payment where Payment.Kuri_Cust_ID = @kuri_Cust_id
    select @totaltillnow = isnull(SUM(NextLotAmount),0) from Kuri.Kuri_GivenDetails
    inner join Kuri.kuri_Customer  
    on Kuri_GivenDetails.kuri_Id  = kuri_Customer.kuri_ID 
     where kuri_Customer.kuri_Cust_id =  @kuri_Cust_id
     if((@payed + @amt) < @totaltillnow)
        set @isVallid = 1
        RETURN @isVallid
    END;
GO

ALTER TABLE [Kuri].[Payment]  WITH CHECK ADD  CONSTRAINT PaymentCheck CHECK (kuri.fnGetAge(kuri_Cust_ID,amt) >= 1 )
GO

error :

The ALTER TABLE statement conflicted with the CHECK constraint
«PaymentCheck». The conflict occurred in database «MERP», table
«Kuri.Payment».

Table structure is like this

CREATE TABLE [Kuri].[Payment](
    [payment_ID] [int] IDENTITY(1,1) NOT NULL,
    [payment_Date] [date] NOT NULL,
    [bill_No] [nvarchar](25) NOT NULL,
    [Kuri_Cust_ID] [int] NOT NULL,
    [vr_ID] [int] NOT NULL,
    [amt] [decimal](14, 2) NULL,
    [created_ID] [int] NULL,
    [created_Date] [datetime] NULL,
    [modified_ID] [int] NULL,
    [modified_Date] [datetime] NULL,
    [authorized_ID] [int] NULL,
    [authorized_Date] [datetime] NULL,
  CONSTRAINT [PK_Payment] PRIMARY KEY CLUSTERED 
  ([payment_ID] ASC)

 ALTER TABLE [Kuri].[Payment]  WITH CHECK ADD  CONSTRAINT [FK_Payment_kuri_Customer] FOREIGN KEY([Kuri_Cust_ID])
 REFERENCES [Kuri].[kuri_Customer] ([Kuri_Cust_ID])

 ALTER TABLE [Kuri].[Payment] CHECK CONSTRAINT [FK_Payment_kuri_Customer]

Scenario:

You are working as SQL Server developer, you are asked to add Check Constraint to one existing table dbo.Employee on FName column and write logic for Check Constraint so it should always accept alphabets.

When you tried to add Check Constraint, you got below error.

Msg 547, Level 16, State 0, Line 19
The ALTER TABLE statement conflicted with the CHECK constraint «Chk_dbo_Employee_FName». 
The conflict occurred in database «YourDatabaseName», table «dbo.Employee», column ‘FName’.

Solution:

Let’s generate the scenario first for the error. Create sample dbo.Employee table with some sample data.

--Create Table  
use YourDatabaseName
go
Create table dbo.Employee
(
FName VARCHAR(100) Not Null,
LName VARCHAR(100),
StreetAddress VARCHAR(255)
)
--Insert data in sql table
insert into dbo.Employee(FName,LName,StreetAddress)
values ('Aamir','Shahzad','xyz address')
go
insert into dbo.Employee(FName,LName,StreetAddress)
values ('Raza A',Null,'abc address')
go

Now run the alter table statement to add Check Constraint.  Once you will execute this statement you will get above error. as existing data does not qualify for Check Constraint. We have space in first name for ‘Raza A’ and our Check Constraint says that the data in FName should be always alphabets.

Alter table dbo.Employee
Add Constraint Chk_dbo_Employee_FName
Check (FName not like '%[^a-z]%')

1) First Solution: Correct Existing Data

Fist solution can be, you find the data that does not qualify for Check Constraint and correct that and then add Check Constraint.

2) If business don’t want to fix the existing data and want to implement Check Constraint from moving forward, you can create the Check Constraint with Nocheck. By doing that it will not validate existing data against our Check Constraint rule but only apply to new data.

Alter table dbo.Employee with nocheck
Add Constraint Chk_dbo_Employee_FName
Check (FName not like '%[^a-z]%') 

Let’s insert couple of records and check if our Constraint is working as expected.

insert into dbo.Employee(FName,LName,StreetAddress)
values ('Test 123',Null,'test address')
go

insert into dbo.Employee(FName,LName,StreetAddress)
values ('Najaf',Null,'test address')
go

The first insert will fail as it does not qualify with our Check Constraint rule. Second record will be inserted successfully. Let’s check the data in table now.

Картинка с сайта: www.techbrothersit.com

How to add Check Constraint to Column with Existing Data in SQL Server

Video Demo : How to fix error the Alter table statement conflicted with the Check Constraint

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!