检查调用函数在更新时不起作用的约束 [英] Check Constraint that calls function does not work on update

问题描述

我创建了一个约束，以防止一个表中的分配超过另一表中的库存（请参阅上一个问题中的详细信息此处）。
但是由于某种原因，该约束仅在我插入新分配时才按预期工作，但在更新时并不能防止违反。

I created a constraint that prevents allocations in one table to exceed inventory in another table (please see the details in my previous question Here). But for some reason the constraint works as expected only when I insert new allocations, but on update it does not prevent violating.

这是我的约束条件：

([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

谢谢。

添加：

这是我的表：

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)

以下语句执行应该失败，但是不会：

The following statement execution should fail, but it does not:

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

推荐答案

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

Well well, I just learned something.

因此，事实证明使用CHECK CONSTRAINTS和UPDATES，只有在CONSTRAINT中引用的列之一更改时，才会检查CONSTRAINT。

在您的情况下，您的约束正在检查将 ItemID 传递给的UDF。

In your case, your CONSTRAINT is checking a UDF that you pass ItemID to.

在UPDATE中，大概只是在更改 Allocation 的值，而不是 ItemID ，因此优化程序会认为如果 ItemID 没有变化，则无需检查约束，并且不会，

In your UPDATE, presumably you are only changing the value of Allocation, and not ItemID, so the optimizer thinks "If ItemID didn't change, then there's no need to check the constraint", and it doesn't, and the UPDATE succeeds even though the CONSTRAINT should have failed it.

我通过重建函数和约束并添加 Allocation ：

I tested this by rebuilding your function and Constraint and adding 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

并且：

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

请注意，我必须先删除原始约束，然后截断/重新填充表格，但这没什么，您不需要我向您展示如何做。

Note that I had to DROP the original constraint first and truncate/repopulate the table, but that's nothing that you need me to show you how to do.

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

Also note that Allocation isn't involved in any of the logic of the function. I didn't change the logic at all, I just added a parameter for @Allocation. The parameter never gets used.

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

Then when I did an UPDATE that raised the SUM of Allocation to above the MAX, I got the expected error:

UPDATE语句与CHECK约束
CK_Allocations冲突。冲突发生在数据库 Tab_Test的表
dbo.Allocations中。

The UPDATE statement conflicted with the CHECK constraint "CK_Allocations". The conflict occurred in database "Tab_Test", table "dbo.Allocations".

为什么？因为即使 @Allocation 在函数的逻辑中没有使用，所以 Allocation 列在CONSTRAINT中是被引用的，因此，当 Allocation 的值更改时，优化器会检查约束。

Why? Because even though @Allocation isn't used in the logic of the function, the Allocation column is referenced in the CONSTRAINT, so the optimizer does check the constraint when the value of Allocation changes.

有人认为，由于这种情况，始终最好使用TRIGGER而不是调用UDF的CHECK CONSTRAINT。我不相信，也没有看到任何可重复的实验来证明这一点。但我留给您您要使用哪种方式。

Some have argued that because of things like this, it's always preferable to use a TRIGGER instead of a CHECK CONSTRAINT that calls a UDF. I'm not convinced, and I haven't seen any reproducible experiments that prove it. But I leave it up to you which way you want to go with this.

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

Hopefully this information will prove useful to some future readers.

PS：由于给予了应有的信誉，我在对问题的评论中的论坛帖子中获得了一些帮助，这导致了有关此主题的博客。

PS: Ascribing proper credit, I learned all this with some help from the forum post in my comment on the question, which led to this blog on the subject.

这篇关于检查调用函数在更新时不起作用的约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！