检查调用函数的约束在更新时不起作用

我创建了一个约束，防止一个表中的分配超过另一个表中的库存（请参阅我的上一个问题 https://stackoverflow.com/questions/50726398/how-can-i-create-a-query-constraint）。但由于某种原因，只有当我插入新的分配时，该约束才按预期工作，但在更新时它并不能防止违反。

这是我的限制：

([dbo].[fn_AllocationIsValid]([Itemid]) = 1)

这是函数：

CREATE FUNCTION [dbo].[fn_AllocationIsValid] (@itemId as int)  
RETURNS int  AS  
BEGIN 
DECLARE @isValid bit;

SELECT @isValid = CASE WHEN ISNULL(SUM(Allocation), 0) <= MAX(Inventory) THEN 1 ELSE 0 END
FROM Allocations A 
JOIN Items I ON I.Id = A.ItemId
WHERE I.Id = @itemId
GROUP BY I.Id;

RETURN @isValid;
END

Updated

这是我的表格：

CREATE TABLE [allocations] (
[userID] [bigint] NOT NULL ,
[itemID] [int] NOT NULL ,
[allocation] [bigint] NOT NULL ,
CONSTRAINT [PK_allocations] PRIMARY KEY  CLUSTERED 
(
    [userID],
    [itemID]
)  ON [PRIMARY] ,
CONSTRAINT [FK_allocations_items] FOREIGN KEY 
(
    [itemID]
) REFERENCES [items] (
    [id]
) ON DELETE CASCADE  ON UPDATE CASCADE ,
CONSTRAINT [CK_allocations] CHECK ([dbo].[fn_AllocationIsValid]([Itemid], [Allocation]) = 1)
) ON [PRIMARY]

CREATE TABLE [dbo].[Items](
[Id] [int] NOT NULL,
[Inventory] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO Items (Id, Inventory) VALUES (2692, 336)

INSERT INTO Allocations (UserId, ItemId, Allocation) VALUES(4340, 2692, 336)
INSERT INTO Allocations (UserId, ItemId, Allocation) VALUES(5895, 2692, 0)

以下语句执行应该会失败，但事实并非如此：

update allocations set allocation = 5
where userid = 5895 and itemid = 2692

好吧好吧，我刚刚学到了一些东西。

事实证明使用 CHECK CONSTRAINTS 和 UPDATES，仅当 CONSTRAINT 中引用的列之一发生更改时才会检查 CONSTRAINT。

在您的情况下，您的 CONSTRAINT 正在检查您通过的 UDFItemID to.

在您的更新中，大概您只是更改了值Allocation， 并不是ItemID，所以优化器认为“如果ItemID没有改变，那么就不需要检查约束”，事实并非如此，即使约束应该失败，更新也会成功。

我通过重建你的函数和约束并添加来测试这一点Allocation to it:

ALTER FUNCTION [dbo].[fn_AllocationIsValid] (@itemId as int, @Allocation int)  
RETURNS int  AS  
BEGIN 
DECLARE @isValid bit;

SELECT @isValid = CASE WHEN ISNULL(SUM(Allocation), 0) <= MAX(Inventory) THEN 1 ELSE 0 END
FROM Allocations A 
JOIN Items I ON I.Id = A.ItemId
WHERE I.Id = @itemId
GROUP BY I.Id;

RETURN @isValid;
END

And:

ALTER TABLE [dbo].[Allocations]  WITH CHECK ADD  CONSTRAINT [CK_Allocations] 
CHECK  (([dbo].[fn_AllocationIsValid]([Itemid], Allocation)=(1)))
GO

请注意，我必须先删除原始约束并截断/重新填充表，但这不需要我向您展示如何操作。

另请注意Allocation不涉及该函数的任何逻辑。我根本没有改变逻辑，我只是​​添加了一个参数@Allocation。该参数永远不会被使用。

然后当我进行更新时，提高了总和Allocation到了 MAX 以上，我得到了预期的错误：

UPDATE 语句与 CHECK 约束冲突 “CK_分配”。数据库“Tab_Test”、表中发生冲突 “dbo.分配”。

为什么？因为即使@Allocation isn't used在函数的逻辑中，Allocation列是引用的在 CONSTRAINT 中，因此优化器会在以下值时检查约束：Allocation变化。

有些人认为，由于类似的情况，最好使用 TRIGGER 而不是调用 UDF 的 CHECK CONSTRAINT。我不相信，而且我还没有看到任何可重复的实验来证明这一点。但我把它留给你，你想怎么做就怎么做。

希望这些信息对未来的读者有用。

PS：归因于适当的功劳，我在对问题的评论中的论坛帖子的一些帮助下了解到了所有这些，这导致关于这个主题的博客 http://sqlblog.karaszi.com/be-careful-with-constraints-calling-udfs/.