Introducing the Flexible Data Type in SQLite Database

When you, in SQLite database, create a table you have three choices in declaring the column datatype:

1. with the datatype and STRICT option,

2. without datatype, or

3. with the datatype.

The first choice is just like what you can do in other relational databases. You must declare the datatype of every column, and the data you insert into the columns must be of the datatype declared. This is known as static data typing.

While other relational databases support only the first choice, SQLite offers the other two choices, the dynamic datatype's.

The second choice allows you to create a table without declaring the datatype of some columns or all columns. SQLite decides the datatype to store based on the value of the data. You can add any data value in the columns and even any different data value in different rows.

Even if you declare the datatype (the second choice), they are just your recommendation. SQLite treats them as preferred datatype; it still decides the datatype to store based on the value of the data.

As static or dynamic is specific to a table, in a database you can, to suit your database need, have a mix of static and dynamic tables.

If you want to learn about the SQLite data typing, both static and dynamic datatype, this is the book of choice.

It is a practical book, details of the data typing is clarified and demonstrated by SQL examples.