Working with Whole Numbers in SQLite: A Guide to INT, INTEGER, SMALLINT, and TINYINT

SQLite uses a more flexible system called "affinity" to determine how data is stored. The ".INT" part of these data types creates an INTEGER affinity, but it doesn't specify a storage size.

Here's the key difference:

• INT, INTEGER, SMALLINT, TINYINT: These are all synonyms for the INTEGER affinity. They tell SQLite that the data in that column should be whole numbers whenever possible. However, SQLite can actually store any type of data (text, floating-point numbers, etc.) in that column.
• Storage size: Behind the scenes, SQLite will efficiently store small integers using only 1 or 2 bytes if that's all that's needed. So even though you didn't specify a size like TINYINT, SQLite will use minimal space for small values.

-- Create a table with three columns using different keywords for INTEGER affinity
CREATE TABLE product (
  product_id INTEGER PRIMARY KEY,  -- INTEGER used here
  quantity SMALLINT,               -- SMALLINT used here (but treated as INTEGER)
  rating TINYINT                   -- TINYINT used here (but treated as INTEGER)
);

-- Insert some data with mixed data types
INSERT INTO product (product_id, quantity, rating)
VALUES (123, 100, 5),  -- Integers for all columns
       (456, 'abc', 3.14),  -- Text for quantity, FLOAT for rating (converted to INTEGER)

-- Select data and check the data type of each column using typeof()
SELECT product_id, typeof(product_id), quantity, typeof(quantity), rating, typeof(rating)
FROM product;

This code will create a table with three columns:

• product_id: INTEGER (primary key)
• quantity: INTEGER (although declared as SMALLINT)

The INSERT statement shows that SQLite can store different data types in these columns. However, the typeof() function in the SELECT statement will reveal that everything is converted to the INTEGER storage class internally.

It's important to consider the trade-offs:

• Validation: Adds complexity to your application code but maintains data integrity.
• Custom Functions: Most complex solution requiring C/C++ programming.
• Data Conversion: Doesn't change how SQLite stores data, just how it's presented after retrieval.