SQLite Strict Tables

Summary: in this tutorial, you will learn about SQLite STRICT tables and their important features such as static typing columns.

Introduction to SQLite strict tables

In SQLite, ordinary tables use dynamic typing for columns. This means that the data types of the columns serve as hints rather than strict rules. Consequently, you can store values of any type in columns without strictly adhering to the declared data types.

For example, even if you declare a column with the INTEGER type, you still can store a value of TEXT or BLOB type in that column.

This feature is unique to SQLite, but some developers find it challenging to work with. Therefore, SQLite introduced a strict typing mode for each table in SQLite 3.37.0, released on Nov 27, 2021.

When creating tables, you have the option to enable the strict typing mode for each table separately by using the STRICT keyword:

CREATE TABLE strict_table_name(
    column type constraint, 
    ...
) STRICT;Code language: SQL (Structured Query Language) (sql)

These tables are often referred to as strict tables.

The strict tables follow these rules:

Every column must have one of the following data types: INT, INTEGER, REAL, TEXT, BLOB, and ANY.
When inserting a value into a column, SQLite will attempt to convert the value into the column’s data type. If the conversion fails, it will raise an error.
Columns with the ANY data type can accept any kind of data. SQLite will not perform any conversion for these columns.
PRIMARY KEY columns are implicitly NOT NULL.
The PRAGMA integrity_check and PRAGMA quick_check commands verify the type of the contents of all columns in strict tables and display errors if any mismatches are found.

SQLite strict table examples

Let’s take some examples using strict tables.

1) Basic SQLite strict table example

First, create a strict table called products to store the product data:

CREATE TABLE products(
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL DEFAULT 0
) STRICT;Code language: SQL (Structured Query Language) (sql)

The STRICT keyword after the closing parenthesis ) indicates that the products table is strict.

Second, attempt to insert NULL into the id column:

INSERT INTO products(id, name, price)
VALUES(NULL, 'A', 9.99);Code language: SQL (Structured Query Language) (sql)

SQLite issues the following error:

SQL Error (19): NOT NULL constraint failed: products.idCode language: SQL (Structured Query Language) (sql)

The reason is that the primary key column of a strict table implicitly has the NOT NULL constraint. To make it work, you must provide a valid value for the id column in the INSERT statement.

Third, insert a new row into the products table by providing values for all columns:

INSERT INTO products(id, name, price)
VALUES(1, 'A', 9.99);Code language: SQL (Structured Query Language) (sql)

Alternatively, you can use the auto-increment column by changing the type of the id column to INTEGER PRIMARY KEY.

Fourth, recreate the products table with the type of id column is INTEGER PRIMARY KEY:

DROP TABLE IF EXISTS products;

CREATE TABLE products(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL DEFAULT 0
) STRICT;Code language: SQL (Structured Query Language) (sql)

In this case, if you insert NULL into the id column, SQLite will use the next integer value for insertion.

Fifth, insert two rows into the products table whose values in the id column are NULLs:

INSERT INTO products(id, name, price)
VALUES(NULL, 'A', 9.99);

INSERT INTO products(id, name, price)
VALUES(NULL, 'B', 10.99);Code language: SQL (Structured Query Language) (sql)

Sixth, retrieve data from products table:

SELECT * FROM products;Code language: SQL (Structured Query Language) (sql)

Output:

id | name | price
---+------+--------
1  | A    | 9.99
2  | B    | 10.99
(2 rows)Code language: SQL (Structured Query Language) (sql)

The output indicates that SQLite uses the integers 1 and 2 for the insertion.

Seventh, insert a new row into the products table without providing the value for the id column:

INSERT INTO products(name, price)
VALUES('C', 5.59);Code language: SQL (Structured Query Language) (sql)

Eighth, query data from products table again:

SELECT * FROM products;Code language: SQL (Structured Query Language) (sql)

Output:

id | name | price
---+------+-------
1  | A    | 9.99
2  | B    | 10.99
3  | C    | 5.59
(3 rows)Code language: SQL (Structured Query Language) (sql)

In this example, SQLite uses the next integer in the sequence and inserts it into the id column.

2) Converting data in strict tables

The following example demonstrates how SQLite attempts to convert input data into column data for insertion.

First, attempt to insert a new row into the products table but the price is a text ‘4.99‘:

INSERT INTO products(name, price)
VALUES('D', '4.49');Code language: SQL (Structured Query Language) (sql)

SQLite will convert the text ‘4.49‘ into a real number and insert it into the price column.

Second, retrieve data from the products table:

SELECT * FROM products;Code language: SQL (Structured Query Language) (sql)

Output:

id | name | price
---+------+------
1  | A    | 9.99
2  | B    | 10.99
3  | C    | 5.59
4  | D    | 4.49
(4 rows)Code language: SQL (Structured Query Language) (sql)

Third, attempt to insert a new row into the products table but the price is a text ‘O.99‘ with the first character is O, not zero:

INSERT INTO products(name, price)
VALUES('E', 'O.99');Code language: SQL (Structured Query Language) (sql)

In this case, SQLite cannot convert the ‘O.99‘ into a real number, therefore, it issues the following error and aborts the insert:

Error: cannot store TEXT value in REAL column products.priceCode language: SQL (Structured Query Language) (sql)

3) Strict table vs ordinary table with ANY type

In a strict table, SQLite preserves the input data and does not carry any conversion. For example:

CREATE TABLE t1(c ANY) STRICT;

INSERT INTO t1(c) VALUES('0001');

SELECT c, TYPEOF(c) FROM t1;Code language: SQL (Structured Query Language) (sql)

Output:

c    | TYPEOF(c)
-----+-----------
0001 | text
(1 row)Code language: SQL (Structured Query Language) (sql)

In this example, SQLite inserts the string ‘0001’ into the c column of the t table without any conversion.

However, in the ordinary table, SQLite attempts to convert a string that looks like a number into a numeric value and store it rather than the original string. For example:

CREATE TABLE t2(c ANY);

INSERT INTO t2(c) VALUES('0001');

SELECT c, TYPEOF(c) FROM t2;Code language: SQL (Structured Query Language) (sql)

Output:

c | TYPEOF(c)
--+----------
1 | integer
(1 row)Code language: SQL (Structured Query Language) (sql)

Summary

Use the STRICT keyword to define strict tables.
Strict tables use static typing columns rather than dynamic typing columns.
The INTEGER PRIMARY KEY column in strict tables has implicit NOT NULL constraints.

Was this tutorial helpful ?