Auto-increment is how a database hands out identifiers without you having to manage them: you insert a row, omit the key, and the engine supplies the next value. Every engine does it, none of them spells it the same way, and the details differ in ways that matter — particularly whether a deleted identifier can be handed out again.
How do auto-increment and sequences generate IDs?
SQLite has a special property that the other engines do not: an INTEGER PRIMARY KEY column is the row identifier. Declare it that way and the engine fills it in automatically:
CREATE TABLE plain_log (log_id INTEGER PRIMARY KEY, note TEXT);
INSERT INTO plain_log (note) VALUES ('opened');
INSERT INTO plain_log (note) VALUES ('reviewed');
DELETE FROM plain_log WHERE log_id = 2;
INSERT INTO plain_log (note) VALUES ('closed');
SELECT log_id, note FROM plain_log ORDER BY log_id;
log_id note
------ -----
1 opened
2 closed
Two rows were inserted and the third reused the identifier 2 that the delete freed. That is the default behaviour of a plain INTEGER PRIMARY KEY: the next value is one more than the largest value currently in the table, so removing the largest row makes that number available again.
What does AUTOINCREMENT change?
Adding the AUTOINCREMENT keyword makes the engine keep a permanent record of the highest identifier it has ever issued, in a system table called sqlite_sequence. The identifier is then never reused:
CREATE TABLE audit_log (log_id INTEGER PRIMARY KEY AUTOINCREMENT, note TEXT);
INSERT INTO audit_log (note) VALUES ('opened');
INSERT INTO audit_log (note) VALUES ('reviewed');
DELETE FROM audit_log WHERE log_id = 2;
INSERT INTO audit_log (note) VALUES ('closed');
SELECT log_id, note FROM audit_log ORDER BY log_id;
log_id note
------ -----
1 opened
3 closed
Three, not two. The identifier 2 was burned, and it will never be issued again. The high-water mark is visible in the system table:
SELECT name, seq FROM sqlite_sequence;
name seq
--------- ---
audit_log 3
This matters wherever an identifier appears outside the database: in a printed invoice, a URL, an audit log or an external system that stores the number. If the number can be reused, "order 2" can mean two different orders over time, and every one of those external references becomes ambiguous. The cost of AUTOINCREMENT is a little extra bookkeeping on every insert, and identifiers that grow without bound — which is why the keyword is a deliberate choice rather than a default.
How does each engine spell it?
| Engine | Column definition | Notes |
|---|---|---|
| SQLite | INTEGER PRIMARY KEY | implicit rowid; add AUTOINCREMENT to forbid reuse |
| PostgreSQL | INTEGER GENERATED ALWAYS AS IDENTITY | the modern form; SERIAL and BIGSERIAL still work |
| MySQL | INT AUTO_INCREMENT PRIMARY KEY | the column must be a key; MyISAM behaviour differs from InnoDB |
| SQL Server | INT IDENTITY(1,1) PRIMARY KEY | the seed and increment are explicit |
| Oracle | CREATE SEQUENCE then a trigger or DEFAULT | sequences are standalone objects you can share between tables |
Two of those deserve a sentence. MySQL's InnoDB engine, unlike older MyISAM tables, never reuses an identifier even without AUTO_INCREMENT, because it keeps the high-water mark in the table itself — so the SQLite AUTOINCREMENT keyword has no direct equivalent there. And PostgreSQL's SERIAL is a shorthand that creates a sequence and wires it to the column; GENERATED ALWAYS AS IDENTITY is the modern replacement, and it behaves better when a row is inserted with an explicit identifier.
What is a sequence, exactly?
In PostgreSQL and Oracle a sequence is a standalone database object that hands out numbers, and a column draws from it through a DEFAULT clause. That indirection has a real advantage: several tables can draw from one sequence, so identifiers never collide across the whole database rather than merely within one table. The standard spelling is:
name seq
--------- ---
audit_log 3
That statement is the SQL standard and works in PostgreSQL and Oracle; MySQL has no sequence objects at all, which is why its AUTO_INCREMENT is a column property instead, and SQL Server's IDENTITY is a column property too. The important idea to carry across dialects is the separation between the value generator and the column: the engine owns the counter, the application never computes identifiers, and two sessions cannot be handed the same number.
Why do identifiers skip numbers?
Because the counter is not rolled back with the data. In PostgreSQL a sequence lives outside the transaction, so a transaction that takes identifier 42 and then rolls back does not give 42 back — the next insert gets 43. Identifiers therefore have gaps, and every engineer meets this for the first time with the same reaction: the table is empty and the counter is at 1,038. The behaviour is deliberate. If the counter were transactional, a rollback would have to restore it, and two concurrent transactions could then be handed the same value. Gaps are the price of never issuing a duplicate, and identifiers are not meant to be gapless — anything that counts them, such as a row number in a report, must be generated with ROW_NUMBER rather than with the key.
Quick reference
- SQLite's INTEGER PRIMARY KEY is an implicit rowid and is filled in automatically.
- Without AUTOINCREMENT, SQLite can reissue the identifier of a deleted row.
- AUTOINCREMENT keeps a permanent high-water mark in sqlite_sequence and never reissues.
- PostgreSQL uses GENERATED ALWAYS AS IDENTITY or SERIAL, MySQL AUTO_INCREMENT, SQL Server IDENTITY(1,1).
- Use AUTO_INCREMENT-style generation when identifiers appear outside the database and must never repeat.
Identifier drill: the insurer sends renewal letters quoting the policy reference, and the letters are filed for seven years. Decide whether the identifier of a lapsed policy can be reissued, and write the one sentence you would put in the schema design document to record the decision.