SQLite Forum

about "strict" mode
Login
<blockquote>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.</blockquote>
I wonder if it may be useful to add a way to define new datatypes which STRICT could check. As an example, the json1 extension could define JSON as being TEXT and use the json_valid function to check it. On regular tables it would have no impact (except maybe changing the type affinity of JSON columns to TEXT instead of NUMERIC?), but on STRICT tables it would enforce valid JSON as if the column had a CHECK constraint on it.

This would be handy for backwards compatibility (I for one have code that relies on using the JSON datatype) and arguably allow for more clarity in the schema, if columns can retain their previous datatype.

On the other hand, it would increase the complexity and amount of code, and can be replaced with CHECK constraints and comments on the table schema, descriptive column names, etc.. Given that, I'm not sure it should be done, but I wanted to mention it in case others thought the same (or had better ideas on why it shouldn't be done).