sql server - SQL Table Constraint to prevent column totals exceeding 100 -
i have table contains columns this:
- someid, int pk
- item1weighting, int
- item2weighting, int
- item3weighting, int
i want add constraint table prevents total of 3 "weighting" columns on single row exceeding total value of 100.
i've done quite bit of searching , can't find any suggestions gratefully received.
thanks kev
you can declaratively without resorting 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 add retrospectively existing table
alter table t add constraint ck_weightingnotover100 check ((isnull(item1weighting,0) + isnull(item2weighting,0) + isnull(item3weighting,0)) <= 100)
Comments
Post a Comment