Styjun

I have a table that I'm trying to make sure that an aggregate sum of the inserts adds up to 1 (it's a mixture).

I want to constrain it so the whole FKID =2 fails because it adds up to 1.1.

Currently my constraint is

FUNCTION[dbo].[CheckSumTarget](@ID bigint)
RETURNS bit
AS BEGIN 
 DECLARE @Res BIT
 SELECT @Res = Count(1)
 FROM dbo.Test AS t
 WHERE t.FKID = @ID 
 GROUP BY t.FKID
 HAVING Sum([t.Value])<>1 
 RETURN @Res
END
GO
ALTER TABLE dbo.Test WITH CHECK ADD CONSTRAINT [CK_Target_Sum] CHECK (([dbo].[CheckSumTarget]([FKID])<>(1)))

but it's failing on the first insert because it doesn't add up to 1 yet. I was hoping if I add them all simultaneously, that wouldn't be the case.

This approach seems fraught with problems.

I would suggest another approach, starting with two tables:

• 
  aggregates, so "fkid" should really be aggregate_id
  


• components

Then, in aggregates accumulate the sum() of the component values using a trigger. Maintain another flag that is computed:

 alter table aggregates add is_valid as ( sum_value = 1.0 )

Then, create views on the two tables to only show records where is_valid = 1. For instance:

create view v_aggregates as
 select c.*
 from aggregates a join
 components c
 on a.aggregate_id = c.aggregate_id
 where a.is_value = 1;

Here is a working version of solution

Here is table DDL

create table dbo.test(
 id int,
 fkid bigint,
 value decimal(4,2)
);

The function definition

CREATE FUNCTION[dbo].[CheckSumTarget](@ID bigint)
RETURNS bit
AS BEGIN 
 DECLARE @Res decimal(4,2)
 SELECT @Res = case when sum(value) > 1 then 1 else 0 end 
 FROM dbo.Test AS t
 WHERE t.FKID = @ID 
 RETURN @Res
END

And the constraint defintion

ALTER TABLE dbo.Test WITH CHECK ADD CONSTRAINT [CK_Target_Sum] CHECK ([dbo].[CheckSumTarget]([FKID]) <> 1)

In your example

insert into dbo.test values (1, 2, 0.5);
insert into dbo.test values (1, 2, 0.4);
-- The following insert will fail, like you expect
insert into dbo.test values (1, 2, 0.2);

Note: This solution will be broken by UPDATE statement (as pointed out by 'Daniel Brughera') however that is a known behaviour. A better and common approach is use of trigger. You may want to explore that.

Your actual approach will work this way.....

1. You insert the firts component, the value must be 1


2. You try to insert a second component, it will be rejected because your sum is already 1


3. You update the existing component to .85


4. You insert the next component, the value must be .15


5. You back to step 2. with the third component

Since your constraint only takes care of the FKID column, it will be possible, and you may think that is working....

But.... if you left the process in step 3. your sum is not equal to 1 and is impossible for the constraint to foresee if you will insert the next value or not, even worst, you can update any value to be greater than 1 and it will be accepted.

If you add the value column to your constraint, it will prevent those updates, but you will never be able to go beyond step 1.

Personally I would't do that, but here you can get an approach

1. Use the computed column suggested by Gordon on your parent table. With computed columns you will always get the actual value, so, the parent wont be valid until the sum is equal to one


2. Use this solution to prevent the value to be greater than 1, so, at least you will be sure that any non valid parent is because a component is missing, that can be helpful for your business layer


3. As I mentioned in one comment, the rest of the logic belongs to the business and ui layers

Note as you can see the id and value parameters are not used in the function, but I need them to call them when I create the constraint, that way the constraint will validate updates too

 CREATE TABLE ttest (id int, fkid int, value float)
 go
 create FUNCTION [dbo].[CheckSumTarget](@id int, @fkid int, @value float)
 RETURNS FLOAT
 AS BEGIN 
 DECLARE @Res float
 SELECT @Res = sum(value)
 FROM dbo.ttest AS t
 WHERE t.FKID = @fkid 
 RETURN @Res
 END
 GO
 ALTER TABLE dbo.ttest WITH CHECK ADD CONSTRAINT [CK_Target_Sum] CHECK (([dbo].[CheckSumTarget](id,[FKID],value)<=(1.0)))