GoKawiilPrefer STRICT tables in SQLite
In short: I prefer strict tables in SQLite because they avoid some datatype problems, such as putting text in number columns.
SQLite has a feature that I think is underrated: strict tables. Strict tables help enforce rigid typing, preventing mistakes like putting text into integer columns. I like them, and wrote this post to promote their use!
To make a strict table, add STRICT to the end of its definition. Like this:
-CREATE TABLE people (name TEXT); +CREATE TABLE people (name TEXT) STRICT;
That’s it! But what does it do?
Advantages of strict tables
Broadly, strict tables help enforce rigid types, like other SQL engines do.
Most significantly, strict tables keep you from inserting the wrong type into a column. For example, SQLite normally lets you put text into an INTEGER column, but not with strict tables.
-- Non-strict tables let you put anything anywhere. CREATE TABLE people_nonstrict (age INTEGER); INSERT INTO people_nonstrict (age) VALUES ( 'garbage' ); -- => works fine -- Strict tables don't allow that, which I prefer. CREATE TABLE people_strict (age INTEGER) STRICT ; INSERT INTO people_strict (age) VALUES ( 'garbage' ); -- => error: cannot store TEXT value in INTEGER column
... continue reading