SQL Update conflict with Check constraint error

Question

I'm trying to update some numeric values in stored procedure

SET SubTotal = SubTotal + @ExtCost
                , GST = SubTotal * 0.05
                , Total = SubTotal + GST
            WHERE JobNumber = @JobNumber

@ExtCost and @JobNumber are all valid and work fine.

I get this error:

The UPDATE statement conflicted with the CHECK constraint "ck_SubTotalandTotal"

The check constraint is Total > SubTotal

Logic seems fine to me, but I have no idea why it's not working :(

Answer 1

Take a record with the following values for example:

• SubTotal: 10
• GST: 0.5
• Total: 10.5

Now you execute your Update with an @extCost of 2

SET 
  SubTotal = SubTotal + @ExtCost, 
  GST = SubTotal * 0.05, 
  Total = SubTotal + GST
WHERE JobNumber = @JobNumber

During an update, the current values are taken to fill in the column values, so this is the same as

SET 
  SubTotal = 10 + 2, 
  GST = 10 * 0.05, 
  Total = 10 + 0.05
WHERE JobNumber = @JobNumber

So (subtotal) 12 > (total) 10.05 resulting in a constraint issue.

What you want is this to take the calculated values into account, so you need to do the calculations per field.

SET 
  SubTotal = SubTotal + @ExtCost, 
  GST = (SubTotal + @ExtCost) * 0.05, 
  Total = (SubTotal + @ExtCost) + ((SubTotal + @ExtCost) * 0.05)
WHERE JobNumber = @JobNumber

or shorter

SET 
  SubTotal = SubTotal + @ExtCost, 
  GST = (SubTotal + @ExtCost) * 0.05, 
  Total = (SubTotal + @ExtCost) * 1.05
WHERE JobNumber = @JobNumber