Difference between INT PRIMARY KEY and INTEGER PRIMARY KEY SQLite

INT PRIMARY KEY vs. INTEGER PRIMARY KEY in SQLite: A Deep Dive

Explore the subtle yet significant differences between INT PRIMARY KEY and INTEGER PRIMARY KEY in SQLite, focusing on their impact on rowid behavior and auto-increment functionality.

When defining primary keys in SQLite, you might encounter two seemingly similar syntaxes: INT PRIMARY KEY and INTEGER PRIMARY KEY. While both will successfully create a primary key, there's a crucial distinction that affects how SQLite manages the underlying rowid and auto-incrementing behavior. Understanding this difference is vital for efficient database design and avoiding unexpected issues, especially when dealing with auto-generated identifiers.

The Implicit ROWID and Primary Keys

SQLite is unique in that every table, by default, has a special 64-bit signed integer column called rowid. This rowid acts as a unique identifier for each row within the table. If you declare a column as PRIMARY KEY, SQLite will attempt to use that column as the rowid if certain conditions are met. This is where the difference between INT and INTEGER becomes apparent.

flowchart TD
    A[Table Creation] --> B{Primary Key Defined?}
    B -- Yes --> C{PK Type is INTEGER?}
    C -- Yes --> D[PK becomes ROWID Alias]
    C -- No (e.g., INT, TEXT) --> E[PK is separate column, ROWID still exists]
    B -- No --> F[ROWID implicitly created]

SQLite's ROWID assignment logic based on Primary Key type.

INT PRIMARY KEY: A Separate Column

When you declare a column as INT PRIMARY KEY, SQLite treats it as a regular column with a PRIMARY KEY constraint. It will enforce uniqueness and non-nullability, but it will not automatically alias this column to the internal rowid. Instead, the rowid will still exist as a separate, hidden column, and your INT PRIMARY KEY column will be distinct from it. This means SQLite will manage two separate unique identifiers for each row: your declared INT PRIMARY KEY and the internal rowid.

CREATE TABLE my_table_int (
    id INT PRIMARY KEY,
    name TEXT
);

INSERT INTO my_table_int (id, name) VALUES (1, 'Alice');
INSERT INTO my_table_int (id, name) VALUES (2, 'Bob');

-- You can still access rowid explicitly
SELECT id, name, rowid FROM my_table_int;

Example of a table with INT PRIMARY KEY.

INTEGER PRIMARY KEY: The ROWID Alias

The magic happens with INTEGER PRIMARY KEY. When a column is declared with the exact type INTEGER PRIMARY KEY (case-insensitive, so integer primary key also works), SQLite treats this column as an alias for the internal rowid. This means that your declared INTEGER PRIMARY KEY column is the rowid. There isn't a separate rowid column; instead, any reference to your INTEGER PRIMARY KEY column is effectively a reference to the rowid.

CREATE TABLE my_table_integer (
    id INTEGER PRIMARY KEY,
    name TEXT
);

INSERT INTO my_table_integer (name) VALUES ('Charlie'); -- id is auto-assigned by rowid
INSERT INTO my_table_integer (name) VALUES ('David');

-- id and rowid will be the same value
SELECT id, name, rowid FROM my_table_integer;

Example of a table with INTEGER PRIMARY KEY.

AUTOINCREMENT Keyword and Performance

While INTEGER PRIMARY KEY provides auto-incrementing behavior by default, the AUTOINCREMENT keyword adds an extra layer of guarantee. When AUTOINCREMENT is used with INTEGER PRIMARY KEY, SQLite ensures that rowid values are always strictly increasing and never reused, even after deletions. This comes with a slight performance overhead, as SQLite must maintain a separate table to track the largest rowid ever used. For most applications, INTEGER PRIMARY KEY without AUTOINCREMENT is sufficient and more performant.

CREATE TABLE my_table_autoincrement (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    data TEXT
);

INSERT INTO my_table_autoincrement (data) VALUES ('Item A');
INSERT INTO my_table_autoincrement (data) VALUES ('Item B');
DELETE FROM my_table_autoincrement WHERE id = 1;
INSERT INTO my_table_autoincrement (data) VALUES ('Item C');

-- id values will be 2, 3 (1 is not reused)
SELECT id, data FROM my_table_autoincrement;

Using AUTOINCREMENT to prevent rowid reuse.

Summary of Differences

The choice between INT PRIMARY KEY and INTEGER PRIMARY KEY boils down to whether you want your primary key to be an alias for SQLite's internal rowid or a completely separate column. The INTEGER PRIMARY KEY approach is generally preferred for simple auto-incrementing integer primary keys due to its efficiency and direct mapping to rowid.

Key differences between INT PRIMARY KEY and INTEGER PRIMARY KEY.