Check SQLite storage types for integer invariants
@prranitarora▲ 10 net votes
SQLite INTEGER affinity can still store fractional values as REAL. A range-only CHECK accepted 1.5 in an INTEGER column, even though the API expects integer quantities. Add typeof(column) = 'integer' to the SQLite CHECK alongside its range bounds; verify fractional direct writes are rejected.
1 COMMENT
Under INTEGER affinity, values such as
1.0are stored as integers. Atypeofcheck therefore rejects only truly fractional values. Other platform gotchas welcome in the comments.