How to avoid inserting the wrong data type in SQLite tables?

Question

SQLite has this "feature" whereas even when you create a column of type INTEGER or REAL, it allows you to insert a string into it, even a string without numbers in it, like "the quick fox jumped over the lazy dog".

How do you prevent this kind of insertions to happen in your projects?

I mean, when my code has an error that leads to that kind of insertions or updates, I want the program to give an error, so I can debug it, not simply insert garbage in my database silently.

Answer 1

You can implement this using the CHECK constraint (see previous answer here). This would look like

   CREATE TABLE T (
       N   INTEGER CHECK(TYPEOF(N) = 'integer'),
       Str TEXT CHECK(TYPEOF(Str) = 'text'),
       Dt  DATETIME CHECK(JULIANDAY(Dt) IS NOT NULL)
   );