0

I'm trying to understand a failure on my CHECK constraint in SQL Server 2008 R2 (the same problem occurs on SQL Server 2012).

My sql command just update the amount by 126.3 on two columns and the constraint checks if the sum of two columns match a third column.

Below are the steps to reproduce the problem:

CREATE TABLE FailedCheck ( item VARCHAR(10), qty_total DOUBLE PRECISION, qty_type1 DOUBLE PRECISION, qty_type2 DOUBLE PRECISION )
ALTER TABLE FailedCheck ADD CONSTRAINT TotalSum CHECK(qty_total = (qty_type1 + qty_type2));
INSERT INTO FailedCheck VALUES ('Item 2', 101.66, 91.44, 10.22);
UPDATE FailedCheck SET qty_total = qty_total + 126.3, qty_type1 = qty_type1 + 126.3

Column qty_total must contain the sum of (qty_type1 and qty_type2). All columns are 'Double precision'. If I change the value from 126.3 to 126, it works, I've tested other values (int and double) and couldn't understand why sometimes it works and sometimes doesn't.

What's wrong with my CHECK constraint ?

PS: Sorry for my english, it's not my primary language.

André Casteliano
  • 614
  • 1
  • 7
  • 11
  • 2
    Why isn't `qty_total` just a computed column (and why aren't they `DECIMAL` with fixed precision, because [this is what happens with `FLOAT`/`DOUBLE`](http://classicasp.aspfaq.com/general/why-does-3-2-1-5-4-7000000000000002.html))? This is like having redundant data - a form field for your birthday, and then a different field for your age, and then complaining when they're don't match. Since you can always calculate age from birthdate, asking for age too makes very little sense (never mind storing it and having to maintain it). – Aaron Bertrand Jan 26 '15 at 20:56
  • 1
    Well, `DOUBLE PRECISION` is an approximate data type, you should be using exact data types for this kind of check constraints – Lamak Jan 26 '15 at 20:56
  • You can also see more information [in this thread](http://stackoverflow.com/questions/1056323/difference-between-numeric-float-and-decimal-in-sql-server). – Aaron Bertrand Jan 26 '15 at 21:04

1 Answers1

3

You decided for a floating point data type which only holds an approximate value - quite precise, but only up to an extent. 1.3 may well be stored as 1.299999999999998 or something along the lines. So the sum for the approximate values of 91.44 and 10.22 may happen to be exactly the approximate value for 101.66, but may also be very slightly different.

Never compare floating point values with the equal sign (=).

And better don't use floting point types in the first place, if not really, really needed. Use DECIMAL instead.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73