SQLite: What is the use of specifying data types?

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?

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

StackExchange Database Administrators Q#203220, answer score: 11