ALTER TABLE 语句冲突

问题描述

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 约束冲突支付宝".数据库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]

推荐答案

正如错误明确指出的那样:表中存在违反检查约束的行.

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

由于您对 kuri.fnGetAge(kuri_Cust_ID,amt) >= 1 的检查约束测试，您可以使用

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

修复或删除这些行，然后你应该没问题，你的 ALTER TABLE 命令应该可以工作

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

这篇关于ALTER TABLE 语句冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！