SQLite has STRICT tables | Writing Developer

I didn’t know that SQLite tables are dynamically typed by default. You can declare a type for a column, but the database will happily store whatever data you put in it!

Luckily, SQLite provides a STRICT option that enforces the declared type.

It works for both INSERT and UPDATE operations:

CREATE TABLE table_strict (age INTEGER) STRICT;

INSERT INTO table_strict (age) VALUES ('ciao');
-- Runtime error: cannot store TEXT value in INTEGER column table_strict.age (19)

INSERT INTO table_strict (age) VALUES (38);
UPDATE table_strict SET age = 'ciao' WHERE age = 38;
-- Runtime error: cannot store TEXT value in INTEGER column table_strict.age (19)

Thanks to Evan Hahn for the TIL!