Generated identity columns assign identifier values during insertion using an engine-specific mechanism such as AUTO_INCREMENT, identity or an SQLite integer primary key.
Applications usually should not calculate a new id by reading the largest existing id and adding one. Two concurrent writers can choose the same number. A database-generated key handles allocation, while the key constraint establishes uniqueness.
This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.
Set up the practice database
Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.
Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40),
manager_id INTEGER
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20) NOT NULL,
created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
(1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
(3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
(101,1,10,'paid','2026-01-01'),
(102,1,30,'pending','2026-01-02'),
(103,2,20,'paid','2026-01-02'),
(104,3,NULL,'pending','2026-02-01'),
(105,3,20,'paid','2026-02-02'),
(106,NULL,5,'paid','2026-02-03');An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.
The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.
How AUTO_INCREMENT and identity columns works
A generated identifier separates row identity from descriptive business values. A name, date or amount can change without changing the row’s key. Omit the generated column from normal INSERT column lists so the database allocates it. The generated value may then be used for related child rows. Retrieve it through the mechanism documented for the same connection or statement, not by querying the overall largest id after insertion.
MySQL uses an integer column with AUTO_INCREMENT. PostgreSQL supports GENERATED ... AS IDENTITY and older sequence-based serial declarations. SQL Server uses IDENTITY(seed,increment). SQLite INTEGER PRIMARY KEY normally aliases its internal rowid and can generate a value when omitted. These syntaxes are not interchangeable. SQLite’s special rowid alias requires the relevant INTEGER PRIMARY KEY form; INT PRIMARY KEY does not have precisely the same identity behaviour.
Why generated ids have gaps
Generated numbers are not a promise of a gap-free sequence. Rollbacks, failed inserts, deleted rows, caching and allocation rules can leave gaps in server engines. A transaction can receive a larger id before another transaction commits a smaller one, so id order is not a reliable universal commit-time ordering. Use a timestamp and suitable transaction semantics when the business question concerns chronology.
SQLite AUTOINCREMENT adds a specific policy that avoids reuse of previously committed maximum rowids and has extra bookkeeping. It is usually unnecessary for ordinary row identity and still does not promise a perfectly consecutive series. PostgreSQL identity ALWAYS and BY DEFAULT modes differ in how explicit supplied ids are handled. SQL Server normally requires IDENTITY_INSERT controls for explicit identity values. When importing historical ids, define the preservation policy and adjust generators through the engine’s supported procedure if needed.
MySQL, PostgreSQL, SQL Server and SQLite
For MySQL declare id INTEGER PRIMARY KEY AUTO_INCREMENT. PostgreSQL can use id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY. SQL Server can use id INTEGER IDENTITY(1,1) PRIMARY KEY. Retrieve ids with the appropriate RETURNING, OUTPUT or connection-local generated-id feature; SQLite last_insert_rowid() and MySQL LAST_INSERT_ID() are connection-scoped examples.
Check the engine-specific syntax and version requirements in SQLite AUTOINCREMENT and rowid, PostgreSQL identity columns.
Practice task and worked solution
In SQLite, insert a second name into the generated_customers table created above. Read both records in id order.
Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.
INSERT INTO generated_customers (name) VALUES ('Finn');
SELECT id,name FROM generated_customers ORDER BY id;id | name
1 | Ema
2 | FinnThe second insert again omits the key. In this fresh uninterrupted demonstration it receives two. That observed sequence illustrates ordinary allocation, but the lesson does not infer that real systems can never have gaps or reuse values under different policies.
In the fresh SQLite example, insert two additional names while omitting ids and inspect the allocations. Delete the highest row in a disposable copy and compare ordinary INTEGER PRIMARY KEY behaviour with an AUTOINCREMENT table. That experiment shows why no-reuse policy and gap-free numbering are different requirements; neither sample should be generalised into a universal transaction chronology.
SQL AUTO_INCREMENT and Identity Columns: Generated IDs Syntax
AUTO_INCREMENT and identity columns
-- SQLite example: INTEGER PRIMARY KEY generates the id.
CREATE TABLE generated_customers (id INTEGER PRIMARY KEY,name TEXT NOT NULL);
INSERT INTO generated_customers (name) VALUES ('Ema');
SELECT id,name FROM generated_customers;SQL AUTO_INCREMENT and Identity Columns: Generated IDs Summary
- Let the database generate ids:
AUTO_INCREMENTin MySQL,GENERATED ... AS IDENTITYin PostgreSQL,IDENTITY(1,1)in SQL Server. - In SQLite, an
INTEGER PRIMARY KEYcolumn receives an id automatically when you leave it out. - Read the new id with
RETURNING,OUTPUTor the driver's last-insert-id function on the same connection. - Gaps in generated ids are normal and don't mean rows are missing.
- Never calculate
MAX(id) + 1yourself: two writers can choose the same value.
SQL AUTO_INCREMENT and Identity Columns: Generated IDs Examples
Each example shows the code, the output it produces and what each part does.
An id generated by SQLite
The fresh SQLite table has no rows, and the INSERT omits id. SQLite allocates one for the new record. This is a specific SQLite example rather than a claim that the same CREATE syntax generates keys in PostgreSQL or SQL Server.
-- SQLite example: INTEGER PRIMARY KEY generates the id.
CREATE TABLE generated_customers (id INTEGER PRIMARY KEY,name TEXT NOT NULL);
INSERT INTO generated_customers (name) VALUES ('Ema');
SELECT id,name FROM generated_customers;id | name
1 | EmaCommon SQL AUTO_INCREMENT and Identity Columns: Generated IDs Mistakes
Calculating MAX(id) + 1 yourself
SELECT MAX(id)+1 followed by INSERT is vulnerable to concurrent writers selecting the same proposed value. It also mishandles an empty table unless carefully guarded. Use the engine’s generated-key mechanism and a primary-key constraint rather than treating a read-and-add operation as atomic allocation.
SQL AUTO_INCREMENT and Identity Columns: Generated IDs in Real Projects
Linking an order to its lines
A checkout service can insert the parent order, retrieve its generated key from the same transaction and use that value in each order-line insert. The identity is a technical link, not necessarily the customer-visible invoice number. A legally or operationally controlled numbering policy may require a separate design.
Retrieve a generated key from the insertion’s own statement or connection and use it for child records in the same controlled workflow. Never infer the new key from the highest visible id, which another writer may change. Keep customer-facing numbering separate if it has legal or operational sequencing requirements beyond ordinary row identity.
SQL AUTO_INCREMENT and Identity Columns: Generated IDs FAQ
Are generated ids always consecutive?
No. Gaps are normal: rolled-back inserts, failed inserts, deleted rows and cached values all skip numbers. They do not establish missing business records. Use a separate, carefully controlled sequence if regulations require gap-free invoice numbers.
Does every integer primary key auto-generate?
No. PostgreSQL, MySQL and SQL Server need their explicit generation feature, otherwise you must supply the id. SQLite has special INTEGER PRIMARY KEY behaviour that generates ids, and it only works with exactly that type name.
Should I expose a sequential id publicly?
It depends on the application's identifier policy: sequential ids reveal roughly how many orders or users exist. Many applications show a separate random public identifier, such as a UUID. Either way, access control must never depend on an id being difficult to guess.
What does AUTOINCREMENT add in SQLite?
Without it, SQLite may reuse the id of the most recently deleted highest row: delete id 2 from ids 1 and 2, insert again, and the new row gets 2. Adding AUTOINCREMENT makes SQLite remember the largest id ever used, so ids are never reused, at a small performance cost.
Can I insert a row with a specific id into an identity column?
Usually, yes, with care. MySQL and SQLite accept an explicit id; PostgreSQL's GENERATED BY DEFAULT accepts one, while GENERATED ALWAYS needs OVERRIDING SYSTEM VALUE; SQL Server needs SET IDENTITY_INSERT table ON. Afterwards, check that the generator won't later produce the same value.
SQL AUTO_INCREMENT and Identity Columns: Generated IDs Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
What id does Finn receive in the fresh uninterrupted SQLite example?
SQL AUTO_INCREMENT and Identity Columns: Generated IDs Quiz
2 questions to check what you learned.
Why avoid MAX(id)+1 for key allocation?
- Because MAX cannot read a primary-key column
- Because adding one is always forbidden even inside a controlled allocator
- Concurrent writers can choose the same next value
Does SQLite require AUTOINCREMENT for ordinary generated rowids?
- No, INTEGER PRIMARY KEY can generate them without it
- Yes, no omitted key can ever be generated without AUTOINCREMENT
- Yes, and AUTOINCREMENT guarantees consecutive ids with no gaps