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

Popular posts from this blog

design - Custom Styling Qt Quick Controls -

Unable to remove the www from url on https using .htaccess -