How to implement a check constraint against more than one row

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.

As a data modeler I am a big fan of constraints. Data consistency is important, eh?

 

A check constraint works on one column on one row. For example, if you have a column Percentage you can add a check constraint saying it can only contain a number between 1 and 100.

 

Recently I designed a database where in one table a fund could have many rows. Each row contained a column Percentage The sum of percentage for all rows for a particular fund could never be over 100%. So, how to implement a check constraint on that?

 

After some thinking I got a vision, if I use a function I can check all rows for a certain fund.

 

The function:
CREATE FUNCTION dbo.CheckPercentage(@val char(1)) RETURNS integer AS BEGIN DECLARE @Sum int DECLARE @Return int SELECT @Sum = SUM (Percentage) FROM RiskFunds WHERE Fund = @val IF @Sum > 100 SET @Return = 0 ELSE SET @Return = 1 RETURN @Return END  
The table:
CREATE TABLE RiskFunds ( Fund char(1) NOT NULL, Percentage tinyint NOT NULL CONSTRAINT RiskFundsPercentage CHECK (Percentage BETWEEN 1 AND 100) )  
Add the function as a constraint:
ALTER TABLE RiskFunds ADD CONSTRAINT CheckTest CHECK (dbo.CheckPercentage(Fund)= 1)  
Test it:

 

INSERT INTO dbo.RiskFunds VALUES('X', 101) --fails INSERT INTO dbo.RiskFunds VALUES('A', 99) INSERT INTO dbo.RiskFunds VALUES('B', 100) INSERT INTO dbo.RiskFunds VALUES('C', 100) INSERT INTO dbo.RiskFunds VALUES('C', 1) --fails INSERT INTO dbo.RiskFunds VALUES('A', 1) --ok INSERT INTO dbo.RiskFunds VALUES('A', 1) --fails