How can I create a query constraint

Question

I have a table Items, which has fields Inventory and ItemId (the primary key).

And I have another table Allocations, which has fields Allocation and ItemId, which is a foreign key referencing Items.ItemId.

Thus many Allocations can relate to one Item.

I wonder how I can add a constraint to not allow SUM(Allocation) in the Allocations table for the same ItemId to accede Items.Inventory for that ItemId. I could find only very simple examples, so I am not sure how to approach this problem.

Answer 1

Implementation for Tab Allerman's answer See demo

CREATE FUNCTION dbo.fnc_IsValid( @ItemId Int)     RETURNS BIT
AS 
BEGIN 
   DECLARE @flag BIT = 1
   SELECT @flag =
   case 
       when 
           ISNULL(SUM(Allocation),0) < =MAX(Inventory)
       then 1
       else 0
   end
   FROM Allocations A (NOLOCK) JOIN Items I
   on A.Itemid=I.Itemid AND I.ItemId=@ItemId
   group by I.ItemId


   RETURN      @flag

 END

 go
 create table Items(ItemId int , Inventory int);
insert into Items values
(1,35),(2,10);

create table Allocations (Allocation int , Itemid int );

ALTER TABLE dbo.Allocations  ADD CONSTRAINT [CK_inventoryCheck] CHECK  ((dbo.fnc_IsValid(Itemid)=1))
go

insert into Allocations values
(10,1),(20,1),(5,1);


select * from Allocations;

Answer 2

You can create a CHECK CONSTRAINT on the Allocations table that calls a function.

Pass the ItemID to the function and have the function populate a local variable with the Items.Inventory for that ItemId.

After that, have the function do a SELECT that gets the SUM(Allocation) in the Allocations table for the same ItemId, and return True (1) if the SUM is greater than the Inventory, or else False (0).

In the CHECK CONSTRAINT, just test whether the Function returns False.