SQL Auto Increment
Many tables use a generated integer identifier. In SQLite, INTEGER PRIMARY KEY can generate one when it is omitted. The AUTOINCREMENT keyword adds a stronger no-reuse rule with extra overhead; it is not required for ordinary generated keys.
Understand the idea
This scratch table uses the simpler SQLite form. After each INSERT, the database chooses a row identifier. Identifiers are for identity, not a promise of gap-free numbering. MySQL and MariaDB use AUTO_INCREMENT, SQL Server uses IDENTITY, and PostgreSQL supports generated identity columns. Use your driver's generated-key API in application code.
Where to try it
Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
CREATE TABLE notes (note_id INTEGER PRIMARY KEY, body TEXT NOT NULL);
INSERT INTO notes (body) VALUES ('Check stock'), ('Pack order');
SELECT note_id, body FROM notes ORDER BY note_id;
Read the result
The two rows receive identifiers 1 and 2 in this fresh database. You did not need to calculate the next number in application code.
Try one change
Delete a note and insert another in a disposable run. Observe the behaviour without treating it as a gap-free counter guarantee.