When should a SQLite table use an INTEGER PRIMARY KEY?

In a rowid table, a single-column primary key declared with the exact type name INTEGER aliases the table’s signed 64-bit rowid, except for the documented INTEGER PRIMARY KEY DESC quirk. [S1]

Use it when you want that built-in row identifier to be the table’s key. For example:

CREATE TABLE task (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

If you insert NULL for id, SQLite chooses a rowid for the aliased key. [S1] That can save you from supplying an ID for that insertion pattern. The rule depends on the table and declaration: it does not apply to WITHOUT ROWID tables, and the DESC form is an exception. [S1]

No. SQLite requires the exact declared type name INTEGER for rowid-alias behavior. INT and BIGINT do not create the alias; they behave as ordinary columns with integer affinity and a unique index. [S1]

Here’s the distinction at a glance:

Declaration              Rowid alias in a rowid table?
id INTEGER PRIMARY KEY    Yes, except for the documented DESC quirk
id INT PRIMARY KEY        No; ordinary integer-affinity column with a unique index
id BIGINT PRIMARY KEY     No; ordinary integer-affinity column with a unique index

Choose INTEGER PRIMARY KEY when you want the alias. Choose INT or BIGINT only if an ordinary integer-affinity primary-key column is what you intend; don’t assume those type names are interchangeable with INTEGER for this behavior. [S1]

Yes. This disposable script creates two tables, inserts NULL into the aliased key, and supplies an explicit value for the ordinary integer-affinity key. Run it in a scratch database; the DROP TABLE statements remove existing tables with these names.

DROP TABLE IF EXISTS demo_integer;
DROP TABLE IF EXISTS demo_int;

CREATE TABLE demo_integer (
  id INTEGER PRIMARY KEY,
  label TEXT
);

CREATE TABLE demo_int (
  id INT PRIMARY KEY,
  label TEXT
);

INSERT INTO demo_integer (id, label) VALUES (NULL, 'rowid alias');
INSERT INTO demo_int (id, label) VALUES (7, 'ordinary integer column');

SELECT id, label FROM demo_integer;
SELECT id, label FROM demo_int;

The first insert asks SQLite to choose a rowid for the aliased key; the second supplies an ordinary column value. [S1] The example compares the declarations and insertion patterns, but it is not a full test of an application’s constraints or migration behavior.

Check the schema rather than relying on how the key looks. For rowid-alias behavior, confirm that the table is a rowid table, the key is a single column, and its declared type is exactly INTEGER. [S1] Also check for the documented INTEGER PRIMARY KEY DESC exception. [S1]

A short design checklist:

1. Decide whether you want the key to alias the rowid.
2. If so, use a single-column INTEGER PRIMARY KEY on a rowid table.
3. Check that the declaration does not use the documented DESC exception.
4. If the table is WITHOUT ROWID, do not expect a rowid alias.
5. Treat INT and BIGINT as ordinary integer-affinity columns, not alternative spellings for the alias. [S1]

For a composite key or a WITHOUT ROWID design, assess the table definition separately; the rowid-alias pattern does not apply there.