SQLite: What is the use of specifying data types?

Question

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?

Answer 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' (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).

• Manngo: 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.
• Vérace: 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.
• Boppity Bop: does this mean that all those shiny things you are so excited about @Vérace involve marking every data field with a "actual" type? or does sqlite re-evaluate every field to determine its "actual" type on every operation (select, sort, insert, update)? I mean how would sqlite engine to know what to do with '6&%$$$#3' ? so it spends what? 20% of its CPU and IO time serving so called "developers" who cant be bothered to define their data models.. is that it?
• Bojan Krkic: sqlite now supports "strict" tables that enforce datatypes. sqlite.org/stricttables.html

Answer 2

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

• Timmmm: That doesn't really answer the question though because you don't need to specify the column type. E.g. create table f(foo CHECK(typeof(foo) = 'real') is ok even though I didn't specify the type of foo.
• screwtop: @Timmmm: I agree, it doesn't, but I thought it a useful complement to Verace's answer, and it was too substantial to include as a comment.

Answer 3

SQLite's dubious decision to ignore column types (they call it a "feature"!) does make it a little more pointless to specify them, but there are still a few reasons to do so:

1. Documentation - at least you know what the type is supposed to be even if it isn't enforced at all.
2. Tool support - the column types can be read programatically, and tools (e.g. SQLiteStudio) can be more helpful if they know the types.
3. Interoperability with other databases - SQL isn't really standard and I think most queries only work on one database, but if you ever come to port your schema to some other database it will be a lot less work if you have types specified.

If you don't care about those then there is no point at all. You can enforce the types using CHECK constraints, but that is independent from the actual column type so isn't really relevant to this question.

SQLite 3.37.0 (2021年11月27日) finally added support for actually enforcing these via the STRICT option.