SQLite Forum

about "strict" mode
Login
The STRICT table-option does three things:

  1.  Enforce authoritarian type-checking when inserting new content
      into tables.

  2.  Restrict column data type names in CREATE TABLE statements
      to one of INT, INTEGER, REAL, BLOB, TEXT
      so that we always know exactly which datatype is allowed in
      that column.

  3.  Require that all fields of a PRIMARY KEY be NOT NULL.

A "PRAGMA strict=ON" statement could do (1) and (3).  But as the
CREATE TABLE statement might already exist in the schema prior to the
PRAGMA, it cannot do (2).  This leads to an interesting conundrum:

> ~~~~
CREATE TABLE t1(a DATE, b JSON);
PRAGMA strict=ON;
INSERT INTO t1 VALUES(a,b) VALUES(?1,?2);
~~~~

Since SQLite does not know anything about "DATE" and "JSON", what
enforcement decisions does it impose on the INSERT?  Does it treat
an unknown datatype as "ANY"?  If that is the case, then if you 
misspell "INTGER" or "INTIGER" does that mean you get to insert any
value you want into your integer column?

Perhaps your intent was that "PRAGMA strict=ON" would rescan the schema
and fail if any table anywhere in the schema has an unrecognized datatype
on a column?