SQL ALTER TABLE 语句冲突
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8192857/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The ALTER TABLE statement conflicted
提问by marc_s
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".
ALTER TABLE 语句与 CHECK 约束“PaymentCheck”冲突。冲突发生在数据库“MERP”，表“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]
回答by marc_s
As the error clearly states: there are rows in your table that violateyour 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
由于您的检查约束测试kuri.fnGetAge(kuri_Cust_ID,amt) >= 1，您可以使用以下命令找到违反此检查约束的行
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 TABLEcommand should work
修复或删除这些行，然后你应该没问题，你的ALTER TABLE命令应该可以工作
