In SQLITE, does specifying the integer type for Primary Keys matter considering that primary keys must have unique values?

Question

When someone asked the difference between integer types in SQLITE:

What is the difference between SQLite integer data types like int, integer, bigint, etc.?

The answer declared it unimportant for SQLITE because:

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.

A SqlLite "integer" can hold whatever you put into it: from a 1-byte char to an 8-byte long long.

When I think of storing integers into a container, the signature of the container can vary with the same value.

0000 0000 0000 0001 = 1
          0000 0001 = 1

Conversely an unsigned integer and an integer can have the same signature but different values:

1111 1111 1111 1111 1111 1111 1111 1111 = -1
1111 1111 1111 1111 1111 1111 1111 1111 = 4294967295

And so I am a bit confused whether it matters for primary keys if I specify the type, because the manual states:

A primary key is a field in a table which uniquely identifies the each rows/records in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.

From this, I have to assume that declaring the specific integer type for a column, being one of:

INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8

is important because I assume that datatypes can have exact same signatures with different values and vice versa, thus violating the must contain unique values specification somehow.

So ultimately my question is, will declaring the datatype of a primary key to be specifically be one of TINYINT SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8 make any difference whatsoever?

I need to know this because I am making a key value store with SQLITE, and want to have the ability to set keys for all the possible datatypes. If there is no difference between TINYINT and INTEGER, then I won't bother having TINYINT as a possible key datatype.

Answer 1

The column type INT, INTEGER, WHATEVER (you can specify virtually any column type) has little bearing, it's an indication of what is to be stored in the column. However, it does not set the type as with one exception (to be discussed) of data that can be stored. In short any type (bar the exception) of data can be stored in any column (irrespective of the defined column type).

• see 3.1 Determination of Column Affinity in the link below

SQL does not differentiate between stored values other than the storage class (null,integer,real,text,blob), if stored as an INTEGER then it is an integer bound only by the limitations of it being stored in at most 8 bytes (64 bit signed).

• see 2. Storage Classes and Datatypes in the link below

The exception is the use specifically of INTEGER PRIMARY KEY or INTEGER with the column set as the primary key at the table level. The value stored MUST be an integer otherwise a DATATYPE MISMATCH will occur.

• as per Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class. ( also in 2. Storage Classes and Datatypes)

So ultimately my question is, will declaring the datatype of a primary key to be specifically be one of TINYINT SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8 make any difference whatsoever?

Not with the listed types (TINYINT ....) as the types all contain INT they will have a type affinity of INTEGER and the column will NOT be an alias of the rowid column.

If you included INTEGER in the list then YES it will make a difference as the column will then be an alias of the rowid column (i.e. it is INTEGER PRIMARY KEY). The column will also be restricted to being an integer value (the columns using the other listed types will not be restricted to integer values).

You may wish to refer to Datatypes in SQLite

The following SQL demonstrates some of the above:-

DROP TABLE IF EXISTS example;
CREATE TABLE IF NOT EXISTS example (
    rowid_alias_must_be_unique_integer INTEGER PRIMARY KEY, -- INTEGER PRIMARY KEY makes the column an alias of the rowid
    col_text TEXT,
    col_integer INTEGER,
    col_real REAL,
    col_BLOB BLOB,
    col_anyother this_is_a_stupid_column_type  -- will have a type affinitiy of NUMERIC
);

/* INSERTS first row with a negative rowid */
INSERT INTO example VALUES (-100,'MY TEXT', 340000,34.5678,x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff',100);
/* All subsequent inserts use the generated rowid */
/* the same value is inserted into all the other columns */
INSERT INTO example (col_text,col_integer,col_real,col_blob,col_anyother) VALUES
    ('MY TEXT','MY TEXT','MY TEXT','MY TEXT','MY TEXT'),
    (100,100,100,100,100),
    (34.5678,34.5678,34.5678,34.5678,34.5678),
    (x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff',x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff',x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff',x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff',x'f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff')
;

SELECT 
    *,
    rowid, 
    typeof(rowid_alias_must_be_unique_integer),
    typeof(col_text), 
    typeof(col_integer),
    typeof(col_real),
    typeof(col_blob),
    typeof(col_anyother)
FROM example
;
/* WILL FAIL as rowid alias is not an integer */
INSERT INTO example VALUES('a','a','a','a','a','a');
DROP TABLE IF EXISTS example;

The result of the first SELECT will be :-

• Note that blobs are handled/displayed according to how the tool (Navicat for SQLite) handles the display of blobs.

The last INSERT fails because the value being inserted into the rowid alias is not an integer value e.g. :-

/* WILL FAIL as rowid alias is not an integer */
INSERT INTO example VALUES('a','a','a','a','a','a')
> datatype mismatch
> Time: 0s

• Note that the answer has not dealt with the intricacies of how the column affinity may effect the extraction of data.