I'm using SQLite and I've got a bunch of fields representing measures in millimeters that I'd like to limit to 1 number after decimal point (e.g. 1.2 ; 12.2 ; 122.2 and so on).

I've seen such things as putting DECIMAL(n,1) as the type for example and I tried it but it doesn't seem to constraint the value (I suppose it's because it's not an actual SQLite type).

Do I need to migrate to MySQL for it to work?

CodePudding user response：

You can make a CHECK constraint using the ROUND function. Declare the column as:

mm REAL CHECK(mm = ROUND(mm, 1))

But note that the underlying representation is still a binary floating-point number, with the usual caveats about accuracy.

CodePudding user response：

MySQL's DECIMAL(nn,1) will round to 1 decimal place for storing. That's not the same as a constraint.

When displaying data, your app should round the result to a meaningful precision. (One decimal place is arguably over-kill for weather readings.)

In general, measurements (not money) should be stored in FLOAT. This datatype (in MySQL and many other products) provides 7 "significant digits" and a reasonably high range of values.

FLOAT has sufficient precision when used for latitude and longitude to distinguish two vehicles, but not enough precision to distinguish two people embracing.

(Sorry, I can't speak for SQLite. If FLOAT is available then I recommend you use it and round on output.)