Comment by bruce511 | Hacker News Reader

Comment by bruce511 | Hacker News Reader

Parent Full thread

Firstly, while it's the default, it's not mandatory. As the author discovered you can use STRICT to make the columns typed, and types to be enforced.

The reason it remains unaltered by default is because one of the goals (and accomplishments) of SQLite is that the on-disk-data-file is completely backwards compatible, and cross-platform. This is a very important feature in some situations, and not lightly tossed aside because some old default or system is not in vogue anymore.

Put another way, "default to what's best for all users collectively" is not strictly equivalent to "default to what's most intuitive to unfamiliar users".

The latter is the luxury of end-user software unburdened by decades of legacy compatibility obligations.

STRICT only works for simple types though as the article noted, so you can't do

    CREATE TABLE mytable (
      id INTEGER PRIMARY KEY, 
      created DATETIME, 
      mything JSON
    ) STRICT;

That's the point of STRICT isn't it? 2 of the 3 types in your SQL statement aren't valid sqlite data types.

The point is that they are types supported by most other sql databases and supported as input/output by sqlite functions, but you can't validate that stored data is the correct type.

Reply on news.ycombinator.com