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?
Best Answer
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.
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:
Another reason:
Also:
N.B. "for the most part..."
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:
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' (SQLite stores BLOBs and numbers as binary just like every other SQL database engine) 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 succinct 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).