# How to implement a check constraint against more than one row

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 `