SQL Auto Increment: MySQL, PostgreSQL, SQL Server and SQLite
SQL auto increment gives every new row a unique number without you choosing it. Learn the syntax in MySQL, PostgreSQL, SQL Server and SQLite, how to read the new ID, why gaps appear, when IDs get reused, how to reset a counter, and what happens at the maximum value, with SQLite tests we ran.
- SQL auto increment has a different keyword in every database:
AUTO_INCREMENT,IDENTITY,SERIALorAUTOINCREMENT. - IDs are unique, not gap-free. Deletes, failed inserts and rollbacks leave holes, so never use them as a row count or invoice sequence.
- In SQLite, a plain
INTEGER PRIMARY KEYreused a deleted top ID; addingAUTOINCREMENTprevented that but made 300,000 inserts about 1.5 times slower. - Read the new ID with the database’s own function or
RETURNING, never withMAX(id).
Almost every table needs a primary key, and the easiest one is a number the database hands out automatically: 1, 2, 3 and so on. SQL auto increment does exactly that. Yet the feature is spelled differently in every database, and its guarantees are often misunderstood. Teams expect IDs without gaps, are surprised when an ID gets reused, or read the newest ID in a way that breaks under load.
We ran the SQLite examples on SQLite 3.50.4 through Python’s sqlite3 module and show their real output. The MySQL, PostgreSQL and SQL Server syntax follows each vendor’s documentation and was not run here. For key basics, see primary key vs foreign key.
What is auto increment in SQL?
Auto increment is a column property that tells the database to generate a new, larger number for each inserted row when you do not supply one. It is almost always used for a surrogate primary key: a meaningless ID that identifies the row, separate from business data such as an email address that might change.
Auto increment syntax by database
| Database | Column definition | Read the new ID |
|---|---|---|
| MySQL / MariaDB | id INT AUTO_INCREMENT PRIMARY KEY |
LAST_INSERT_ID() |
| PostgreSQL | id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY (or older SERIAL) |
RETURNING id |
| SQL Server | id INT IDENTITY(1,1) PRIMARY KEY |
SCOPE_IDENTITY() or OUTPUT |
| Oracle 12c+ | id NUMBER GENERATED ALWAYS AS IDENTITY |
RETURNING id INTO |
| SQLite | id INTEGER PRIMARY KEY (optionally AUTOINCREMENT) |
last_insert_rowid() or RETURNING id |
The SQL standard form is GENERATED ... AS IDENTITY, supported by PostgreSQL, Oracle and others. PostgreSQL’s SERIAL still works but is a shortcut that creates a separate sequence object; identity columns are the recommended choice for new tables.
SQLite: INTEGER PRIMARY KEY vs AUTOINCREMENT
SQLite is unusual. Any column declared exactly INTEGER PRIMARY KEY becomes an alias for the table’s internal rowid and gets automatic values already. The AUTOINCREMENT keyword adds one extra guarantee: an ID is never reused, even after the highest row is deleted. It must be spelled precisely:
CREATE TABLE bad (id INT PRIMARY KEY AUTOINCREMENT);
-- sqlite3.OperationalError: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
INT is not good enough; it must be INTEGER. Note also that INT PRIMARY KEY without AUTOINCREMENT does not become a rowid alias at all, a classic SQLite gotcha.
Do auto increment IDs get reused? Our test
We created two tables, inserted four rows into each, deleted the row with the highest ID (4) and inserted one more:
CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE b (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
-- a: [(1,'x'), (2,'y'), (3,'z'), (4,'v')] ID 4 reused
-- b: [(1,'x'), (2,'y'), (3,'z'), (5,'v')] ID 4 skipped
-- sqlite_sequence: [('b', 5)]
Without AUTOINCREMENT, SQLite picks one more than the current largest rowid, so after deleting the top row the old ID came back. With AUTOINCREMENT, SQLite stores the highest ID ever used in the internal sqlite_sequence table and always goes beyond it. Reuse matters when IDs leak outside the database: a URL, a cache key or a log entry could suddenly point at a different record. MySQL InnoDB and SQL Server identity columns normally do not reuse values; MySQL before version 8.0 could, after a server restart, because the counter was rebuilt from MAX(id).
Getting the ID you just inserted
cur = con.execute("INSERT INTO a(name) VALUES ('w')")
print(cur.lastrowid) # 4
con.execute("SELECT last_insert_rowid()").fetchone() # (1000,) after a later insert
con.execute("INSERT INTO r(n) VALUES ('a'), ('b') RETURNING id").fetchall()
# [(1,), (2,)]
Each connection tracks its own last inserted ID, so it is safe under concurrency. RETURNING, available in SQLite since 3.35 and in PostgreSQL for years, gives you the IDs of every inserted row, which is the cleanest option for multi-row inserts. Never use SELECT MAX(id) afterwards: another session may insert between your two statements and you would return the wrong row.
Inserting your own ID
INSERT INTO a (id, name) VALUES (100, 'manual');
INSERT INTO a (name) VALUES ('next'); -- gets id 101
You can usually supply an explicit value, and the counter then continues above it. That jump is permanent: the counter will not go back to fill 5 to 99. In SQL Server you need SET IDENTITY_INSERT table ON first, and PostgreSQL’s GENERATED ALWAYS rejects manual values unless you add OVERRIDING SYSTEM VALUE. In PostgreSQL, inserting manual IDs can also leave the sequence behind the real data, causing duplicate-key errors later, until you move it forward with setval.
Why auto increment IDs have gaps
- Deleted rows leave holes that are not refilled.
- Rolled-back transactions: in MySQL, PostgreSQL and SQL Server, a number handed out to a transaction that later rolls back is lost. Sequences are deliberately not transactional, so concurrent inserts never have to wait for each other.
- Failed inserts, for example a unique constraint violation, can consume a value.
- Caching: SQL Server and Oracle pre-allocate blocks of values; a restart can skip a block, which is why SQL Server identity values sometimes jump by 1,000.
- Bulk and upsert statements such as MySQL
INSERT ... ON DUPLICATE KEY UPDATEmay reserve values they never use. See our SQL upsert guide.
SQLite behaved differently in our rollback test: inserting a row in a transaction, rolling back and inserting again gave ID 6, the same number the rolled-back row had received, because the sqlite_sequence update was rolled back too. Behaviour differs, so the only safe assumption everywhere is: unique and increasing, but not continuous.
Tip: if you need a gap-free number, such as an invoice number required by tax rules, generate it separately in a locked counter table inside the same transaction, not from the primary key.
Resetting the counter
DELETE FROM b; -- next ID in b: 7 (AUTOINCREMENT remembers)
DELETE FROM a; -- next ID in a: 1 (plain rowid starts over)
UPDATE sqlite_sequence SET seq = 999 WHERE name = 'b'; -- next ID in b: 1000
In SQLite, deleting all rows reset the plain table to 1 but not the AUTOINCREMENT table, which continued from 7. Editing sqlite_sequence moved it to 1000. In other databases (not run here):
- MySQL:
ALTER TABLE t AUTO_INCREMENT = 1000;andTRUNCATE TABLEresets to 1. - PostgreSQL:
ALTER TABLE t ALTER COLUMN id RESTART WITH 1000;orTRUNCATE t RESTART IDENTITY. - SQL Server:
DBCC CHECKIDENT ('t', RESEED, 999);andTRUNCATEresets to the seed.
The difference between DELETE and TRUNCATE here is one of several covered in our DELETE vs TRUNCATE vs DROP guide. Never reset a counter below existing IDs, or the next insert will fail with a duplicate key.
What happens at the maximum value?
A 32-bit INT tops out at 2,147,483,647. That sounds huge, but busy event and log tables have hit it, after which every insert fails. Use BIGINT for tables that might grow large; the extra 4 bytes per row are cheap insurance. SQLite always uses 64-bit rowids, and we tested the very top, 9,223,372,036,854,775,807:
-- plain INTEGER PRIMARY KEY after max id: next rowid 561939074613244480 (random unused)
-- AUTOINCREMENT after max id: sqlite3.DatabaseError: database or disk is full
The plain table quietly switched to choosing random unused IDs, so new rows were no longer in insertion order. The AUTOINCREMENT table refused the insert with SQLITE_FULL. You will rarely hit this, but it shows the real guarantee of each option.
Performance cost of AUTOINCREMENT in SQLite
| Table | 300,000 inserts |
|---|---|
INTEGER PRIMARY KEY |
2,135 ms |
INTEGER PRIMARY KEY AUTOINCREMENT |
3,253 ms |
Each AUTOINCREMENT insert also updates sqlite_sequence, which made inserts about 1.5 times slower in our autocommit test. The SQLite documentation itself recommends avoiding the keyword unless you truly need the no-reuse guarantee.
Auto increment IDs vs UUIDs
| Aspect | Auto increment | UUID |
|---|---|---|
| Size | 4 or 8 bytes | 16 bytes |
| Index locality | Excellent, always appends | Random v4 scatters; time-ordered v7 is better |
| Generated | By the database | Anywhere, even offline |
| Guessable | Yes, sequential | No |
| Merging databases | Collisions likely | Practically unique |
Sequential IDs in URLs let anyone guess other records and estimate your growth, so never rely on them for access control. Many teams keep an auto increment key internally and expose a UUID or random public ID. Design trade-offs like this are part of good database design.
Best practices
- Use
BIGINTfor any table that could grow beyond a few hundred million rows. - Prefer standard identity columns in PostgreSQL and Oracle over legacy sequences.
- Read new IDs with
RETURNINGor the session function, neverMAX(id). - Treat IDs as unique labels, not counts, ranks or dates.
- In SQLite, use plain
INTEGER PRIMARY KEYunless IDs must never be reused. - Do not expose sequential IDs as the only protection for private data.
Final thoughts
SQL auto increment saves you from inventing keys, but each database spells it differently and guarantees slightly different things. Every engine promises unique, increasing numbers; none promises numbers without gaps, and SQLite may even reuse them unless you ask otherwise. Pick the right type size, read new IDs correctly, keep business sequences separate, and the humble ID column will never surprise you. Constraints that protect your keys are covered in SQL constraints.
Frequently asked questions
How do I create an auto increment column in SQL?
Use AUTO_INCREMENT in MySQL, an IDENTITY column in SQL Server, PostgreSQL and Oracle, and INTEGER PRIMARY KEY in SQLite. Each makes the database assign the next number when you insert a row without an ID.
Why are there gaps in my auto increment IDs?
Deleted rows, rolled back transactions, failed inserts and cached values all consume numbers that are never handed out again. Auto increment guarantees unique IDs, not continuous ones.
How do I reset auto increment to 1?
Empty the table with TRUNCATE in MySQL or SQL Server, use RESTART IDENTITY in PostgreSQL, or delete the row from the sqlite_sequence table in SQLite. Never reset below existing IDs.
Does SQLite reuse auto increment IDs?
A plain INTEGER PRIMARY KEY can reuse the highest ID after that row is deleted. Adding the AUTOINCREMENT keyword stops reuse by tracking the largest ID ever used.
How do I get the last inserted ID?
Use LAST_INSERT_ID in MySQL, SCOPE_IDENTITY in SQL Server, last_insert_rowid in SQLite, or a RETURNING clause where supported. Do not use MAX of the ID column, which is unsafe with concurrent inserts.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.