Every id column built since Module 7 has used some form of auto-increment, assigning the next whole number automatically so nothing has to compute or guess it. The auto-increment table in the create-table lesson previewed the five different syntaxes; this lesson covers all five properly, plus resetting the counter and retrieving the id a row just got.
Learning Objectives
- Write an auto-incrementing primary key on every database in this course.
- Retrieve the id a row was just assigned, immediately after inserting it.
- Explain why gaps in auto-incremented values are normal, not a bug.
The Five Syntaxes
| Database | Auto-Incrementing Primary Key |
|---|---|
| MySQL / MariaDB | id INT AUTO_INCREMENT PRIMARY KEY |
| PostgreSQL | id SERIAL PRIMARY KEY (or id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, the modern style) |
| SQL Server | id INT IDENTITY(1,1) PRIMARY KEY |
| SQLite | id INTEGER PRIMARY KEY (auto-increments by default, no extra keyword needed) |
| Oracle (12c+) | id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY |
Two genuinely different underlying mechanisms hide behind these five keywords. MySQL/MariaDB, SQL Server, and SQLite treat auto-increment as a property of the column itself. PostgreSQL and Oracle actually create a separate sequence object behind the scenes, a counter that exists independently of the table, which is why those two also support manually creating and sharing a sequence across multiple tables, something the column-property style can't do.
Getting the Last Inserted id
| Database | Retrieve the Just-Inserted id |
|---|---|
| MySQL / MariaDB | SELECT LAST_INSERT_ID(); |
| PostgreSQL | INSERT INTO departments (name) VALUES ('Legal') RETURNING id; |
| SQL Server | SELECT SCOPE_IDENTITY(); |
| SQLite | SELECT last_insert_rowid(); |
| Oracle (12c+) | INSERT INTO departments (name) VALUES ('Legal') RETURNING id INTO :new_id; |
PostgreSQL's RETURNING clause and Oracle's RETURNING ... INTO are a genuinely different pattern from the other three, the id comes back as part of the same INSERT statement's result, rather than a separate follow-up query. SQL Server's SCOPE_IDENTITY() specifically returns the id from the current session and scope, deliberately avoiding a rare race condition where a trigger on the same table inserts its own row in between.
Resetting the Counter
Each database exposes resetting the counter completely differently, reflecting the same column-property vs sequence-object split from above. This is rarely something to do on a production table with real data, it's mostly useful when clearing out test data and wanting the next insert to start back at 1.
ALTER TABLE departments AUTO_INCREMENT = 1;
ALTER SEQUENCE departments_id_seq RESTART WITH 1;
DBCC CHECKIDENT ('departments', RESEED, 0);Why Gaps Are Normal
An auto-incrementing column is guaranteed to be unique and increasing, but never guaranteed to be gapless or perfectly sequential. A rolled-back transaction, a failed INSERT, or a DELETE on an existing row all leave a permanent gap, the counter itself never goes backward or gets reused. If department ids 1 through 5 exist and department 3 is deleted, a new department inserted afterward gets id 6, not the freed-up 3.
Try It Yourself
Write an auto-incrementing primary key column named id for a new table named reviews, on SQL Server.
CREATE TABLE reviews ( id INT IDENTITY(1,1) PRIMARY KEY );
Frequently Asked Questions
Can an auto-incrementing value ever be reused after a row is deleted?
No, on any of these five databases, the counter only moves forward, it never reuses a value freed up by a deleted row. Reusing ids would risk a deleted row's old id silently referring to a completely different, newer row anywhere it was previously referenced.
Why does PostgreSQL's SERIAL actually create a separate sequence object?
SERIAL is really shorthand that creates an independent sequence (a standalone counter object in the database) and sets the column's default to pull the next value from it. This is why PostgreSQL also supports CREATE SEQUENCE directly and sharing one sequence across multiple tables, a capability column-property-style auto-increment (MySQL, SQL Server, SQLite) doesn't have.
Common Beginner Mistakes
Assuming AUTO_INCREMENT syntax works on PostgreSQL, or SERIAL works on MySQL
Neither works on the other database, this is exactly the five-way fragmentation the table above exists to flag. Always check the correct syntax for the specific target database before writing a CREATE TABLE statement.
Treating a gap in id values as evidence of a bug or data loss
Gaps are a completely normal side effect of rollbacks, failed inserts, and deletes, not a sign anything is actually wrong. An auto-incrementing column only promises uniqueness and increasing order, never a gapless sequence.
Best Practices
- Look up the correct auto-increment syntax for the target database rather than assuming one pattern works everywhere.
- Use RETURNING (PostgreSQL/Oracle) or the equivalent last-insert function immediately after the INSERT, in the same database session, to reliably get the right id.
- Never design logic that depends on auto-incremented ids being gapless or perfectly sequential.
Interview Questions
Explain why PostgreSQL and Oracle's auto-increment implementation is fundamentally different from MySQL's.
MySQL (and SQL Server, SQLite) treat auto-increment as a built-in property of the column itself, tightly coupled to that one table. PostgreSQL's SERIAL and Oracle's GENERATED ALWAYS AS IDENTITY both actually create a separate sequence object behind the scenes, an independent counter that exists on its own and, in PostgreSQL's case, can even be manually created and shared across multiple tables, something the column-property approach has no equivalent for.
Write an INSERT into departments that also returns the newly generated id, on PostgreSQL, without a separate follow-up query.
INSERT INTO departments (name) VALUES ('Legal') RETURNING id; — RETURNING id makes the id come back as part of this same INSERT statement's result set, rather than requiring a second SELECT LAST_INSERT_ID()-style query afterward, PostgreSQL and Oracle (via RETURNING ... INTO) are the only two in this course with that specific capability.
Summary
An auto-incrementing column assigns the next whole number automatically, with five genuinely different keywords across the databases in this course, and two different underlying mechanisms behind them: a column property (MySQL/MariaDB, SQL Server, SQLite) versus an independent sequence object (PostgreSQL, Oracle). Retrieving the just-inserted id, and resetting the counter, both follow the same column-vs-sequence split. Gaps in the sequence are a normal, expected side effect, never a sign of a bug.
That completes Module 8, Constraints: all six real constraint types (NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT), plus the closely related CREATE INDEX and the full AUTO INCREMENT story this course has been previewing since Module 7.
What's Next?
Module 9, Dates, Views, and Security, is next: working with DATE and TIMESTAMP data properly, building reusable VIEWs, and the real security practices, SQL injection, parameters, and prepared statements, that protect a database from the applications writing to it.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.