Check Constraint that calls function does not work on update

Question

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

Here is my constraint:

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

And here is the function:

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

Here are my tables:

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

Answer 1

Well well, I just learned something.

So it turns out that with CHECK CONSTRAINTS and UPDATES, the CONSTRAINT only gets checked if one of the columns referenced in the CONSTRAINT changed.

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

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.

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

And:

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.

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.

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

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

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.

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: 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.