The ALTER TABLE statement conflicted

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]

1 Answer

As the error clearly states: there are rows in your table that violate your check constraint.

Since your check constraint tests for kuri.fnGetAge(kuri_Cust_ID,amt) >= 1, you can find those rows in violation of this check constraint using

 SELECT * FROM Kuri.Payment WHERE kuri.fnGetAge(kuri_Cust_ID, amt) < 1

Fix or delete those rows, and then you should be fine and your ALTER TABLE command should work