What does STRICT mode change in a SQLite table?

STRICT is an option on a table definition that makes SQLite enforce a narrower set of column types. Every column must declare a type, and the permitted names are INT, INTEGER, REAL, TEXT, BLOB, and ANY. [S1]

For a column other than ANY, a value must be NULL (unless another constraint forbids NULL) or have the specified type. SQLite still attempts its usual affinity conversions; if a value cannot be converted losslessly, the insert or update is rejected. [S1]

For example, assuming your application expects an item label to be text and its count to be an integer, a disposable table could be:

CREATE TABLE demo_item (
  label TEXT NOT NULL,
  quantity INTEGER NOT NULL
) STRICT;

The NOT NULL rules here come from the schema, not from STRICT itself. STRICT does not replace ordinary constraints; CHECK, UNIQUE, foreign-key, and other table behavior continue to work as before. [S1]

Not necessarily. SQLite first attempts its usual affinity conversion. STRICT rejects a value when it cannot be losslessly converted to the declared type; it is not a rule that all differently represented input must fail. [S1]

For a small disposable test, try a value that should not be losslessly convertible to an integer:

INSERT INTO demo_item (label, quantity)
VALUES ('pencils', 'not-a-number');

That insert should be rejected for the INTEGER column. The example illustrates the type rule; it is not a substitute for testing the exact inputs your application sends. The documentation excerpt establishes the conversion and rejection rule, but does not specify the outcome for every possible input value. [S1]

In a STRICT table, ANY accepts values of any storage class without coercion. Use it only when accepting varied value types in that column is intentional; it does not mean the other typed columns stop being checked. [S1]

The permitted type names are exactly INT, INTEGER, REAL, TEXT, BLOB, and ANY; names such as VARCHAR are not on that list. [S1] If you are adapting an existing schema, check each declared type rather than assuming a familiar type name is accepted.

One key detail: INTEGER PRIMARY KEY aliases the rowid, but INT PRIMARY KEY does not. [S1] If that rowid behavior matters, use the exact INTEGER PRIMARY KEY spelling rather than changing it to INT PRIMARY KEY.

Check the SQLite version used by the application that will open the database, not just the version installed on your development machine. STRICT is supported beginning with SQLite 3.37.0, released 2021-11-27; earlier versions do not normally recognize the STRICT table option. [S1]

A practical review is: confirm that the runtime is at least 3.37.0; change column declarations to the allowed names; decide whether each column should reject values that cannot be losslessly converted; and add any separate constraints your data needs. Then test a disposable schema with representative inputs. The source establishes the version threshold and type rules, but does not establish which SQLite runtime a particular application uses or whether a proposed schema fits your data.

For example, a minimal table using two allowed types is:

CREATE TABLE demo_note (
  note_id INTEGER PRIMARY KEY,
  body TEXT NOT NULL
) STRICT;

Keep INTEGER PRIMARY KEY if you intend the rowid alias behavior described above. [S1]