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
错误 :
ALTER TABLE语句与CHECK约束“ PaymentCheck”冲突。数据库“ MERP”的表“ Kuri.Payment”中发生了冲突。
表结构是这样的
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]
该错误清楚地表明:表中的某些行 违反 了检查约束。
由于您的检查约束条件测试为kuri.fnGetAge(kuri_Cust_ID,amt) >= 1,因此您可以使用来找到违反此检查约束条件的行
kuri.fnGetAge(kuri_Cust_ID,amt) >= 1
SELECT * FROM Kuri.Payment WHERE kuri.fnGetAge(kuri_Cust_ID, amt) < 1
修复或删除这些行,然后就可以了,您的ALTER TABLE命令应该可以正常工作
ALTER TABLE