SQLite Forum

About STRICT tables

About STRICT tables

(1) By Simon Slavin (slavin) on 2021-08-22 15:56:50 [link] [source]

A column about strict tables has appeared in the draft site:


Possibly a good idea to raise a discussion on it. I'll open with a few things:

  1. The syntax to mark a table as strict puts the word STRICT at the end of the CREATE line. Is there a good reason why the word doesn't go after CREATE ? I'm likely to 'lose' a word at the end of the line: my eyes could pass over it and not see it.
  2. Could correct use of single and double quotes be enforced for the strict CREATE commands ? Or is that better associated with the strict PRAGMA ?
  3. Could the strict mode make all tables STRICT, whether declared STRICT or not ?

For reference, here's the page about strict mode:


(2) By David Jones (vman59) on 2021-08-22 18:13:37 in reply to 1 [link] [source]

The draft page has INT and INTEGER as separate types while the wiki implies they are synonyms. The draft page also lacks ANY as an allowed type.

(3) By anonymous on 2021-08-23 12:27:31 in reply to 1 [link] [source]

One more question that is not immediately clear to me. 4. Are these type names the only thing that may be used as types or can they still be decorated with a length? And will this length be enforced? E.g. CREATE TABLE ShortNumber ( N INTEGER(3) ) STRICT

Only values between -999 and 999

(4) By Larry Brasfield (larrybr) on 2021-08-23 12:36:48 in reply to 3 [source]

No such decoration is allowed under STRICT.

(5) By Ryan Smith (cuz) on 2021-08-23 12:48:07 in reply to 3 [link] [source]

To add to Larry's comment and in case it wasn't clear to you before: Length decorations were never used or useful[1] in SQLite although they were allowed for compatibility sake (much like fake TYPE names), hence they won't be allowed in STRICT tables.

[1] Length indicators after Text-affinity type-names (Varchar etc.) were "consulted" for some query optimization in the Query Engine, when given. But they never had any data impact or restrictive qualities.