How do I enable strict mode in SQLite 3.31.1?

user_3343623 0
Viewed 793

I am trying to enable strict mode to require a datatype on every table column and enforce those types. as per this page.

$ sqlite3 ./a_new_database.sqlite
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> PRAGMA strict=ON;
sqlite> CREATE TABLE yay ( col1 TEXT, col2 INT );
sqlite> INSERT INTO yay ( col1, col2 ) VALUES ("this works", "this is the wrong type");
sqlite> SELECT * from yay;
this works|this is the wrong type
sqlite> 
$

Not only can I INSERT the wrong datatype. I can also SELECT it.

I have tried PRAGMA strict=ON; and PRAGMA strict=1;. Neither works. I think I did not correctly enable strict mode.

How do I correctly enable strict mode?

sqlite sql
1 Answers

According to the documentation, you add STRICT to the end of the table declaration:

$ sqlite3
SQLite version 3.37.0 2021-11-27 14:13:22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE yay ( col1 TEXT, col2 INT ) STRICT;
sqlite> INSERT INTO yay ( col1, col2 ) VALUES ("this works", "this is the wrong type");
Error: stepping, cannot store TEXT value in INT column yay.col2 (19)

It should be noted, this is a new feature with SQLite version 3.37.0. On any previous version of SQLite, the table creation will fail. And any previous version will fail to open a database file created with this flag with a "malformed database schema" error, unless you specify the PRAGMA writable_schema=ON pragma before opening the database.

You can use a similar concept of CHECK constraints on older versions of SQLite:

SQLite version 3.31.0 2020-01-22 18:38:59
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE yay ( col1 TEXT CHECK (typeof(Col1) = 'text'), col2 INT CHECK (typeof(Col2) = 'integer'));
sqlite> INSERT INTO yay ( col1, col2 ) VALUES ("this works", "this is the wrong type");
Error: CHECK constraint failed: yay
user_23786430
Related