5

As far as I am aware, SQLite does not enforce defined table data types — it has dynamic typing. For example, it allows you to insert strings into number fields, or to exceed the length of the string.

If that is the case, what is the point in defining data types in the CREATE TABLE statement?

1

There are several rationales in SQLite for defining datatypes, or as SQLite calls them, "storage classes".

If you read this page, it provides an insight into both the function of and reasoning behind SQLite datatypes. In particular, the following sections of that page are interesting.

However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.

I.e. like the C programming language, SQLite allows you to do very stupid things because it also allows you to do very clever things - compare C with Java and SQLite with other RDBMSs for example.

and:

A storage class is more general than a datatype.

Another reason:

In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases should work the same way in SQLite.

Also:

And so for the most part, "storage class" is indistinguishable from "datatype" and the two terms can be used interchangeably.

N.B. "for the most part..."

In order to maximize compatibility between SQLite and other database engines, and so that the example above will work on SQLite as it does on other SQL database engines, SQLite supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required.

So, a very good reason for this genericity is backwards compatibility with virtually all engines on the market - and it the default doesn't suit you, you are free to change the code (Public Domain licence). Another reason why SQLite is the world's most popular RDBMS!

Yet another interesting titbit:

A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. (As an internal optimization, small floating point values with no fractional component and stored in columns with REAL affinity are written to disk as integers in order to take up less space and are automatically converted back into floating point as the value is read out. This optimization is completely invisible at the SQL level and can only be detected by examining the raw bits of the database file.)

So, a column with REAL affinity (as opposed to INTEGER) actually does make a functional difference to the results of a query and/or calculation. For example, the string '456.567' (all data in SQLite is stored as TEXT) would be treated as 456.567 for NUMERIC calculations, but no attempt would be made to coerce '6&%$$$#3' into any sort of NUMERIC.

An important assumption that SQLite makes is that the programmer is king and not the engine! If, for some reason, you want to convert '6&%$$$#3' to an INTEGER, then you can make up your own algorithm to so, but SQLite assumes that if you wish to treat '6&%$$$#3' as an INTEGER, then you must have a good reason for making that choice!

A succint way of putting this is that SQLite gives you very powerful tools, but like any powerful tool, one has to exercise caution and restraint. Basically, SQLite says "you are an adult - if you want to shoot yourself in the head with the nailgun I've given you, go ahead, but don't blame me if you get hurt!".

Apart from the SQLite page, these links are also helpful (1, 2).

  • So, to put it in simplistic terms, data is not enforced, but type affinity is supported. Declaring data types makes the code more compatible with traditional SQL code. I also noticed that it does have some influence on sort order: numbers stored in a varchar column will be sorted alphabetically (1,10,2) while those stored in an integer column will be sotred numerically (1,2,10); non numeric strings come last. Thanks for the details. – Manngo Apr 7 '18 at 12:11
  • The sorting thing makes perfect sense to me. It might be an interesting project (grad maybe?) to introduce datatype enforcement into the SQLite codebase? I don't know if any external code would be accepted though since I seem to remember reading that the only contributors are D. Richard Hipp and an Australian whose name I can't remember. I also know that Hipp is concerned about code bloat, but with a few ifdefs and/or even some alternative downloads, there's no reason that there should be any effect on the overall size of the codebase for those who want to keep it tight. – Vérace Apr 7 '18 at 19:51
  • The sorting thing makes perfect sense to me. It might be an interesting project (grad maybe?) to introduce datatype enforcement into the SQLite codebase? I don't know if any external code would be accepted though since there are only 3 contributors to the system. I also know that Hipp is concerned about code bloat, but with a few ifdefs and/or even some alternative downloads, there's no reason that there should be any effect on the overall size of the codebase for those who want to keep it tight. – Vérace Apr 7 '18 at 20:10
8

Further to Vérace’s answer:

While SQLite doesn’t enforce storage classes as data types, you can apply stricter type-checking using typeof() in a constraint, e.g.

Some_Quantity real check (typeof(Some_Quantity) = 'real')

You do lose automatic type coercion, e.g. it would prevent the values '1' or 1 from being stored in a real column (you would have to specify it as 1.0). However, you can allow multiple types:

Some_Quantity real check (typeof(Some_Quantity) in ('real', 'integer', 'null')

SQLite's type flexibility is also handy for allowing “out-of-band” indicator values that would otherwise not be valid for the column’s type:

Year check (typeof(Year) = 'integer' or Year in ('illegible', 'not disclosed'))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.