SQLite - AUTOINCREMENT

SQLite - The AUTOINCREMENT Keyword

In SQLite, you can create columns that automatically generate a unique integer for each new row. This is commonly used for primary key fields. There are two slightly different ways to achieve this, and understanding the distinction is important for database design.

The Default Behavior: INTEGER PRIMARY KEY

By default, if you declare a column with the type INTEGER PRIMARY KEY, it becomes an alias for the internal rowid. This rowid is a 64-bit signed integer that is automatically assigned and is guaranteed to be unique among all rows in the table.

For most applications, this is all you need. The rowid will autoincrement, and if you delete the row with the highest rowid, that value may be reused for a future insert. This is efficient and sufficient for the vast majority of use cases.

Syntax for Default Behavior

CREATE TABLE table_name(
   ID INTEGER PRIMARY KEY,
   -- other columns...
);

The AUTOINCREMENT Keyword

The AUTOINCREMENT keyword can be added to an INTEGER PRIMARY KEY column. It imposes an additional constraint: the value generated for the primary key will always be strictly greater than the largest value ever used in that column for that table.

This means that even if you delete the row with the highest ID, that ID will never be reused for a new row. SQLite achieves this by keeping track of the highest-used ID in a separate table named sqlite_sequence.

When should you use AUTOINCREMENT? Only when you have a strict requirement that a primary key, once generated, can never be reused. For example, if you expose these IDs in external systems or public URLs and need them to be permanently stable. For internal use, the default behavior is usually preferred as it is slightly more performant and uses less storage.

Syntax for AUTOINCREMENT

CREATE TABLE table_name(
   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   -- other columns...
);

Example: Demonstrating the Difference

Let’s create two tables, one with the default behavior and one with AUTOINCREMENT, to see the difference in action.

-- Table with standard INTEGER PRIMARY KEY (most common case)
CREATE TABLE PROJECTS(
   ID INTEGER PRIMARY KEY,
   NAME TEXT NOT NULL
);

-- Table with AUTOINCREMENT to prevent ID reuse
CREATE TABLE INVOICES(
   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   AMOUNT REAL NOT NULL
);

Now, let’s insert some data, delete the last row, and insert again.

--- Step 1: Insert initial data into both tables ---
INSERT INTO PROJECTS (NAME) VALUES ('Project A'), ('Project B');
INSERT INTO INVOICES (AMOUNT) VALUES (100.50), (250.00);

sqlite> SELECT * FROM PROJECTS;
ID  NAME
--  ---------
1   Project A
2   Project B

sqlite> SELECT * FROM INVOICES;
ID  AMOUNT
--  ------
1   100.5
2   250.0

--- Step 2: Delete the row with the highest ID from both tables ---
DELETE FROM PROJECTS WHERE ID = 2;
DELETE FROM INVOICES WHERE ID = 2;

--- Step 3: Insert a new row into each table ---
INSERT INTO PROJECTS (NAME) VALUES ('Project C');
INSERT INTO INVOICES (AMOUNT) VALUES (99.75);

--- Step 4: Observe the results ---
sqlite> SELECT * FROM PROJECTS;
ID  NAME
--  ---------
1   Project A
2   Project C  -- The ID 2 was REUSED

sqlite> SELECT * FROM INVOICES;
ID  AMOUNT
--  ------
1   100.5
3   99.75      -- The ID 2 was SKIPPED, a new ID 3 was generated

Best Practice

Unless you have a specific, well-defined need for non-reusable IDs, prefer the standard INTEGER PRIMARY KEY without the AUTOINCREMENT keyword. It’s more efficient and aligns with the typical behavior of most database systems.