In most relational database management systems (RDBMSs) the PRIMARY KEY is used to define the unique row identifier for a table. But in SQLite, not all primary keys are handled the same way when it comes to indexing.

Depending on how the primary key is defined in a table, it may or may not show up in the list of indexes returned by the PRAGMA index_list() command. In particular, when the primary key is an INTEGER PRIMARY KEY, SQLite doesn’t explicitly create a separate index for it.

This article will explain why this happens and provide examples with different types of primary key definitions.

The Role of PRAGMA index_list()

The PRAGMA index_list('table_name') command returns a list of all the indexes on a specified table, including both user-defined and system-generated indexes. However, the primary key index may not always appear in this list, depending on how the primary key is defined.

Why the INTEGER PRIMARY KEY Is Not Listed

When a column is defined as an INTEGER PRIMARY KEY, SQLite automatically treats this column as an alias for the rowid. The rowid is a special column in every SQLite table that provides a unique identifier for each row, and it’s implicitly indexed. Therefore, SQLite does not create a separate index for the INTEGER PRIMARY KEY column, and as a result, it does not show up in the PRAGMA index_list() output. The rowid already serves as the primary key, and SQLite optimizes performance by not creating an additional index.

In contrast, when the primary key is defined as other data types (such as TEXT, INT, BIGINT, UNSIGNED INTEGER), SQLite will explicitly create a separate index for that column, and it will appear in the PRAGMA index_list() result.

So if you want to take advantage of the rowid, you must define the primary key as INTEGER PRIMARY KEY exactly, bearing in mind that this will not create a separate index for the column.

Example of Different Primary Key Types

To better understand this behavior, let’s walk through three examples of tables with different types of primary keys: INTEGER PRIMARY KEY, INT PRIMARY KEY, and TEXT PRIMARY KEY.

Table 1: INTEGER PRIMARY KEY

CREATE TABLE users_int_primary (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    username TEXT
);

In this example, the id column is defined as INTEGER PRIMARY KEY. This column is treated as an alias for the rowid, and no additional index is explicitly created for it. Let’s check the indexes using PRAGMA index_list():

PRAGMA index_list('users_int_primary');

Output:

+-----+--------------------------------------+--------+--------+---------+
| seq |                 name                 | unique | origin | partial |
+-----+--------------------------------------+--------+--------+---------+
| 0   | sqlite_autoindex_users_int_primary_1 | 1      | u      | 0       |
+-----+--------------------------------------+--------+--------+---------+

Explanation: The table has an automatic index for the email column (due to the UNIQUE constraint), but the id column, which is an alias for the rowid, does not appear because SQLite does not create a separate index for it.

Table 2: INT PRIMARY KEY

CREATE TABLE users_int (
    id INT PRIMARY KEY,
    email TEXT UNIQUE,
    username TEXT
);

In this case, the id column is defined as INT PRIMARY KEY.

It would be easy to assume that this will behave exactly like INTEGER PRIMARY KEY – after all, both INTEGER and INT result in an INTEGER type. But that assumption would be wrong. The two definitions behave differently. Unlike the INTEGER PRIMARY KEY, INT PRIMARY KEY does not automatically use the rowid. Therefore, SQLite will create an explicit index for this column. The same is true for other INTEGER definitions, such as BIGINT, UNSIGNED INTEGER, etc.

Let’s check the indexes using PRAGMA index_list():

PRAGMA index_list('users_int');

Output:

+-----+------------------------------+--------+--------+---------+
| seq |             name             | unique | origin | partial |
+-----+------------------------------+--------+--------+---------+
| 0   | sqlite_autoindex_users_int_2 | 1      | u      | 0       |
| 1   | sqlite_autoindex_users_int_1 | 1      | pk     | 0       |
+-----+------------------------------+--------+--------+---------+

Explanation: Here, SQLite creates an explicit index named sqlite_autoindex_users_int_1 for the id column, and it appears in the PRAGMA index_list() output. Additionally, there is an automatic index for the email column due to the UNIQUE constraint.

Table 3: TEXT PRIMARY KEY

CREATE TABLE users_text (
    id TEXT PRIMARY KEY,
    email TEXT UNIQUE,
    username TEXT
);

In this example, the id column is defined as TEXT PRIMARY KEY. This column is explicitly indexed, so you will see this index in the PRAGMA index_list() output. Let’s check the indexes using PRAGMA index_list():

PRAGMA index_list('users_text');

Output:

+-----+-------------------------------+--------+--------+---------+
| seq |             name              | unique | origin | partial |
+-----+-------------------------------+--------+--------+---------+
| 0   | sqlite_autoindex_users_text_2 | 1      | u      | 0       |
| 1   | sqlite_autoindex_users_text_1 | 1      | pk     | 0       |
+-----+-------------------------------+--------+--------+---------+

Explanation: Here, there is an explicit index named sqlite_autoindex_users_text_1 for the id column, as well as an automatic index for the email column due to the UNIQUE constraint.

Summary of Results

• INTEGER PRIMARY KEY: Does not create a separate index, because the column is treated as an alias for the rowid, which is automatically indexed by SQLite.
• INT PRIMARY KEY: Creates an explicit index for the id column, as this does not alias the rowid.
• TEXT PRIMARY KEY: Also creates an explicit index for the id column, as this is a non-integer primary key that requires an explicit index.

So to summarize, if the primary key is defined as INTEGER PRIMARY KEY, then there will be no separate index, and therefore when you run PRAGMA index_list() against the table, no index will appear for that primary key.