https://www.sqlite.org/draft/stricttables.html

SQLite
*** DRAFT ***

  * Home
  * Menu
  * About
  * Documentation
  * Download
  * License
  * Support
  * Purchase
  * Search

  * About
  * Documentation
  * Download
  * Support
  * Purchase

[Search Documentation] [                    ] [Go]
STRICT Tables

1. Introduction

SQLite strives to be flexible regarding the datatype of the content
that it stores. For example, if a table column as a type of
"INTEGER", then SQLite tries to convert anything inserted into that
column into an integer. So an attempt to insert the string '123'
results in an integer 123 being inserted. But if the content cannot
be losslessly converted into an integer, for example if the input is
'xyz', then the original string is inserted instead. See the
Datatypes In SQLite document for additional information.

Many developers appreciate the flexible typing rules of SQLite and
use them to advantage. But other developers are aghast at SQLite's
flagrant rule-breaking and prefer the traditional rigid type system
found in all other SQL database engines and in the SQL standard. For
this latter group, SQLite supports a strict typing mode (as of
version 3.37.0 circa pending) that is enabled separately for each
table.

1.1. STRICT Tables

In a CREATE TABLE statement, if the "STRICT" table-option keyword is
added to the end, after the closing ")", then strict typing rules
apply to that table. This means:

 1. Every column must have a datatype. The freedom to specify a
    column without a datatype is removed.

 2. The datatype must be one of:

      + INT
      + INTEGER
      + REAL
      + TEXT
      + BLOB
 3. Content inserted into the column must be either a NULL (assuming
    there is no NOT NULL constraint on the column) or the type
    specified. SQLite attempts to coerce the data into the
    appropriate type using the usual affinity rules, just as
    PostgreSQL does, but if the value cannot be losslessly converted
    in the specified datatype, then an SQLITE_CONSTRAINT_DATATYPE
    error is raised.

 4. Columns that are part of the PRIMARY KEY must be NOT NULL.

                            *** DRAFT ***