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.

Microeconomics

You have enough rep around here to know that posting details is going to help, if not be mandatory for this type of thing. Can you post your table structures and some sample data? Also, any chance you can upgrade your sql server. It has been out of support for nearly a decade now. You are two versions behind the oldest version that is still in support. – Rm

Yes, I know, we are going to upgrade. Unfortunately, it does not depend on me. – Microeconomics

Answer 1

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.

Hasten

Answer 2

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;

Appear

You created an @Allocation parameter for your function and then didn't use it. Better to leave it out. Good implementation otherwise. – Hasten

@Tab Alleman I created the constraint, but it works only on insert, but not on update. On update, it lets me violate the constraint and make Allocations.Allocation as large as I want. Do you have any idea why? – Microeconomics

@DavidShochet You should shift to trigger if you need on update as well – Appear

@DavidShochet, no that shouldn't happen. I would need to see the code of the constraint and the function you created to see if I could reproduce the problem, constraints should apply to updates the same as inserts. There's a pretty good chance you're misinterpreting what you're seeing. – Hasten

Here is my constraint: ([dbo].[fn_AllocationIsValid]([Itemid]) = 1) – Microeconomics

gonna need your function code too. You should probably create a new question for this. – Hasten

In a preliminary test, I am unable to reproduce your issue. Whether I do an INSERT or an UPDATE, I still cannot violate the constraint. Before you create a new question, you might want to look more closely at exactly what you're doing and what else could be causing the result you are getting. – Hasten

Sorry, I have already created the question: #50745813 I tried it even in SQL Server 2016 with the same result... Don't see what the issue can be. If I change the constraint to work the opposite way, I cannot save it, as it immediately finds all my data to be conflicting the constraint. – Microeconomics