AUTO_INCREMENT vs IDENTITY vs Sequences

All three generate numbers for you, but they are three different mechanisms with different behaviours.

MySQL's AUTO_INCREMENT is a column attribute. The column is declared with it and the engine fills in the next value on insert. It is tied to the table, it cannot be shared between tables, and it has gaps — the counter advances even when a transaction is rolled back, because the value is allocated before the commit.

The SQL standard's IDENTITY, also called GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY, is the portable version of the same idea. GENERATED ALWAYS means you may not supply the value yourself; BY DEFAULT means you may, and the identity only fills in when you do not. SQLite has supported it since version 3.31, PostgreSQL since 10, and SQL Server calls it IDENTITY and uses the BY DEFAULT behaviour.

A standalone sequence is an independent database object. You create it once and draw values from it with nextval, and you can share one sequence across several tables, use it to generate something that is not a primary key, or hand out values without touching a table at all. PostgreSQL has sequences natively and also supports the standard's IDENTITY as a wrapper around one; Oracle has sequences and identity columns; MySQL 8 has sequences; SQLite does not have them and has to use AUTOINCREMENT or an application-generated value.

The gap behaviour is worth knowing because it surprises people. With AUTO_INCREMENT and IDENTITY, the counter is not rolled back, so a failed transaction leaves a hole in the sequence. That is deliberate — it avoids two transactions being handed the same value after a rollback — and it means ids are not contiguous. If you need gapless numbering, a sequence gives you more control, at the cost of handling the concurrency yourself.

In SQLite the auto-assigning primary key is an INTEGER PRIMARY KEY, and adding AUTOINCREMENT makes it strictly monotonic and forbids reuse of a deleted id.

CREATE TABLE tickets (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title VARCHAR(160) NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO tickets (title) VALUES ('Login page times out');
INSERT INTO tickets (title) VALUES ('Invoice PDF is blank');
INSERT INTO tickets (title) VALUES ('Export times out');

SELECT id, title FROM tickets ORDER BY id;

Three rows come back with ids 1, 2 and 3. The id column was never mentioned in the INSERT statements — the engine assigned the values.

The AUTOINCREMENT keyword is a small but meaningful difference from a plain INTEGER PRIMARY KEY. Without it, SQLite assigns the next rowid as one more than the current maximum, which means that if you delete the highest-numbered row, the next insert reuses that id. With AUTOINCREMENT, SQLite keeps a separate counter in the table's schema, so ids are never reused. That matters whenever an id has escaped the database — if a support ticket id appears in a URL, a log file or a customer email, a reused id can point somebody at the wrong record.

The dialect names differ but the shape is the same. MySQL uses AUTO_INCREMENT, which must come after the PRIMARY KEY declaration: id INT AUTO_INCREMENT PRIMARY KEY. PostgreSQL uses an identity column: id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, or GENERATED BY DEFAULT if you want to be able to supply the value yourself. SQL Server uses IDENTITY(1,1). The standard spelling that SQLite accepts is the identity column, but the AUTOINCREMENT keyword is the SQLite-native form.

One caveat worth raising in an interview: none of these mechanisms produce contiguous ids under concurrency. Two transactions can be assigned ids 10 and 11, and the transaction holding 10 can commit after the one holding 11, so the order of ids in the table does not match the order of commits. If you need a strict commit ordering, you need a sequence with explicit caching control or a monotonic commit timestamp, not an auto-increment column.