SQL Table Constraint to prevent column totals exceeding 100

Question

I have a table which contains columns like this:

• SomeId, Int PK
• Item1Weighting, Int
• Item2Weighting, Int
• Item3Weighting, Int

I want to add a constraint to the table that prevents the total of the three "weighting" columns on a single row exceeding a total value of 100.

I've done quite a bit of searching and can't find any help so any suggestions would be gratefully received.

Thanks Kev

Answer 1

You can do this declaratively without resorting to triggers.

CREATE TABLE T
(
SomeId  Int PRIMARY KEY,
Item1Weighting  Int,
Item2Weighting  Int,
Item3Weighting  Int,
CONSTRAINT CK_WeightingNotOver100 
    CHECK ((ISNULL(Item1Weighting,0) + 
            ISNULL(Item2Weighting,0) + 
            ISNULL(Item3Weighting,0)) <= 100)
)

Or to add it retrospectively to an existing table

ALTER TABLE T
ADD CONSTRAINT CK_WeightingNotOver100 
    CHECK ((ISNULL(Item1Weighting,0) + 
            ISNULL(Item2Weighting,0) + 
            ISNULL(Item3Weighting,0)) <= 100)