CODEWITHSUNDEEP
mysqlsqliteconstraintsdecimalsqldatatypes
Etsaf
Etsaf

Reputation: 167

Is decimal number constraint possible in SQLite?

Issue

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?

EDIT (solution found)

I used Dan04's answer : it's simple and it works really fine :

► Table is as follow :

CREATE TABLE demo(
a REAL CHECK(a = ROUND(a,1)),
b REAL CHECK(b = ROUND(b,1)),
c REAL GENERATED ALWAYS AS (a+b)
)

► Insert corerct data : INSERT INTO demo (a,b) values (41.4,22.6)

► Insert bad data : INSERT INTO demo (a,b) values (1.45,22.68) outputs :

Execution finished with errors.
Result: CHECK constraint failed: a = ROUND(a,1)
At line 1:
insert into demo (a,b) values (1.45,22.68)

Upvotes: 0

Views: 602

Answers (2)

Rick James
Rick James

Reputation: 142540

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.)

Upvotes: 0

dan04
dan04

Reputation: 91227

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.

Upvotes: 2

Related Questions