SQL Server 2012检查udf上的约束错误

Just trying to make a simple function in SQL Server 2012 to be used as a check constraint. Cant get past this error. Thanks for any help!

只是尝试在SQL Server 2012中创建一个简单的函数用作检查约束。不能错过这个错误。谢谢你的帮助！

The error I receive:

我收到的错误：

Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the CHECK constraint "CheckBatchQuantity". The conflict occurred in database "Ians23_SnackManufacturer", table "dbo.Batch", column 'BatchQuantity'.

消息547，级别16，状态0，行1 ALTER TABLE语句与CHECK约束“CheckBatchQuantity”冲突。冲突发生在数据库“Ians23_SnackManufacturer”，表“dbo.Batch”，列'BatchQuantity'中。

Code:

码：

CREATE FUNCTION udfBatchNumber2
(@BatchQuantity int)

RETURNS int 

AS    

BEGIN
DECLARE @Return int
IF @BatchQuantity >10 
    SET @Return = 0

ELSE
    SET @Return = 1

RETURN @Return 
END

ALTER TABLE Batch
ADD CONSTRAINT CheckBatchQuantity
check ((dbo.[udfBatchNumber](BatchQuantity)) <= 0)

1 个解决方案

#1

The error is due to existing records in Batch table whose BatchQuantity is greater than zero.

该错误是由BatchQuantity大于零的Batch表中的现有记录引起的。

First update/remove the records whose BatchQuantity > 0 then create the check constraint

首先更新/删除BatchQuantity> 0的记录，然后创建检查约束

update Batch
set BatchQuantity = NULL
Where BatchQuantity > 0

ALTER TABLE Batch
ADD CONSTRAINT CheckBatchQuantity
check ((dbo.[udfBatchNumber](BatchQuantity)) <= 0)

or you can create the constraint with NOCHECK which will not check the existing data

或者您可以使用NOCHECK创建约束，该约束不会检查现有数据

ALTER TABLE Batch WITH NOCHECK 
ADD CONSTRAINT CheckBatchQuantity
check ((dbo.[udfBatchNumber](BatchQuantity)) <= 0)

#1