Is it possible to enforce SQLite datatypes?

Question

From my understanding, sqlite's datatype isn't associated to its column but to the data themselves: this practically means that you can insert any data to any column.

Is it possible to prohibit such a behaviour? I mean, I want sqlite to raise an error (or at least a warning) when I accidentally try to insert text to integer, for example.

Answer 1

You can use CHECK constraints and typeof() to check the actual data type:

CREATE TABLE MyTable (
    Col1 INTEGER  CHECK (typeof(Col1) = 'integer'),
    Col2 TEXT     CHECK (typeof(Col2) IN ('text', 'null')
);