Store boolean value in SQLite

There is no native boolean data type for SQLite. Per the Datatypes doc:

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

In SQLite the best you can do is use the integers 0 and 1 to represent false and true. You could declare the column type like this:

CREATE TABLE foo(mycolumn BOOLEAN NOT NULL CHECK (mycolumn IN (0, 1)));

Omit the NOT NULL if you want to allow NULL in addition to 0 and 1.

The use of the type name BOOLEAN here is for readability, to SQLite it's just a type with NUMERIC affinity.

Note that CHECK constraints have been supported since SQLite 3.3.0 (2006).

Here are some example INSERTs that will work: (note how strings and floating point numbers are parsed as integers)

sqlite> INSERT INTO foo VALUES(0);
sqlite> INSERT INTO foo VALUES(1);
sqlite> INSERT INTO foo VALUES(0.0);
sqlite> INSERT INTO foo VALUES(1.0);
sqlite> INSERT INTO foo VALUES("0.0");
sqlite> INSERT INTO foo VALUES("1.0");
sqlite> select mycolumn, typeof(mycolumn) from foo;
0|integer
1|integer
0|integer
1|integer
0|integer
1|integer

and some that will fail:

sqlite> INSERT INTO foo VALUES("-1");
Error: constraint failed
sqlite> INSERT INTO foo VALUES(0.24);
Error: constraint failed
sqlite> INSERT INTO foo VALUES(100);
Error: constraint failed
sqlite> INSERT INTO foo VALUES(NULL);
Error: foo.mycolumn may not be NULL
sqlite> INSERT INTO foo VALUES("true");
Error: constraint failed
sqlite> INSERT INTO foo VALUES("false");
Error: constraint failed