Skip to content
Tutorials Center
My learning

Topics

Lesson 70 of 125 ยท Designing Databases

Auto-Increment and Identity Columns

Let the database number your rows. Learn SQLite's ROWID and AUTOINCREMENT, IDENTITY, SERIAL and AUTO_INCREMENT in other databases, sequences and UUID keys.

Most tables use a surrogate key: an id number that means nothing except "this row". You could pick those numbers yourself, but then two users signing up at the same moment might both grab id 42. Instead, you let the database hand out the next number automatically, safely, even when many connections insert at once.

Every database can do this, but each one spells it differently: INTEGER PRIMARY KEY in SQLite, GENERATED AS IDENTITY or SERIAL in PostgreSQL, AUTO_INCREMENT in MySQL, IDENTITY(1, 1) in SQL Server. This lesson explains how SQLite numbers rows under the hood, how the other databases do it, how sequences work, and when a UUID is a better key than a number.

If you haven't yet, read Primary, Candidate, Composite and Foreign Keys first: it explains why surrogate keys exist.

ROWID: the hidden number in every SQLite table

Every ordinary SQLite table has a hidden 64-bit integer column called rowid. You never declared it, but it's there, and SQLite uses it to store rows in order on disk. You can select it by name:

SQL The hidden rowid column

Press Run (or Ctrl + Enter) to see the result here.

The table has only a body column, yet each row got a rowid of 1, 2 and 3. Looking up a row by rowid is the fastest lookup SQLite can do, because the table itself is a B-tree sorted by it.

INTEGER PRIMARY KEY

When you declare a column as exactly INTEGER PRIMARY KEY, it becomes an alias for rowid. It's the same value stored in the same place, just with a name you chose. If you insert a row without a value for it (or with NULL), SQLite picks one for you: one more than the largest id currently in the table.

SQL Automatic ids with INTEGER PRIMARY KEY

Press Run (or Ctrl + Enter) to see the result here.

Ana and Ben get 1 and 2. Chloe gets the id you supplied, 100, and Dev then gets 101, because the next number is always "largest so far plus one". id and rowid show the same value in every row, because they are the same column. SQLite even labels the rowid result column id.

After an insert you usually need the new id, for example to insert the order lines that belong to a new order. There are two ways to get it:

SQL Getting the new id back

Press Run (or Ctrl + Enter) to see the result here.

last_insert_rowid() returns the id of the most recent insert on this connection, so another user's insert can't confuse it. RETURNING is more flexible: it returns any columns of the inserted rows, works for multi-row inserts and saves a round trip. It's covered in detail in INSERT.

INT PRIMARY KEY is not the same thing. Only the exact type name INTEGER creates a rowid alias. INT PRIMARY KEY, BIGINT PRIMARY KEY and UNSIGNED INTEGER PRIMARY KEY all create an ordinary column with a separate index, and SQLite will not fill it in. Combined with SQLite's "NULL allowed in primary keys" quirk, you can end up with ids that are NULL.

This example shows the trap:

SQL The INT PRIMARY KEY trap

Press Run (or Ctrl + Enter) to see the result here.

Both rows have a rowid, but id is NULL for both. Change INT to INTEGER and run it again to see the difference.

Reused ids and AUTOINCREMENT

Because SQLite picks "largest id plus one", deleting the row with the largest id frees that number, and the next insert takes it again:

SQL Plain INTEGER PRIMARY KEY can reuse ids

Press Run (or Ctrl + Enter) to see the result here.

"New laptop" gets id 3, the number that "Spam filter" used to have. Usually that's harmless, but it can be a real problem if the old id lives on somewhere else: in log files, emails, URLs or another system that still thinks ticket 3 is the spam filter.

Adding the keyword AUTOINCREMENT changes the rule: ids are never reused, even after the largest row is deleted. SQLite remembers the highest id ever handed out in an internal table called sqlite_sequence:

SQL AUTOINCREMENT never reuses ids

Press Run (or Ctrl + Enter) to see the result here.

This time "New laptop" gets id 4, and sqlite_sequence shows that the highest id used for tickets is 4.

Which should you use? SQLite's own advice is to skip AUTOINCREMENT unless you need its guarantee: it makes every insert do a little extra work to update sqlite_sequence. Use it when ids leave the database and must never point at a different row later.

Identity columns in other databases

The SQL standard (since SQL:2003) calls auto-numbered columns identity columns. PostgreSQL, SQL Server, Oracle and Db2 support the standard syntax or something close; MySQL has its own keyword. These snippets are read-only:

SQL
-- PostgreSQL 10+ (standard syntax)
CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

-- PostgreSQL (older style, still very common)
CREATE TABLE customers (
  id bigserial PRIMARY KEY,
  name text NOT NULL
);

-- MySQL / MariaDB
CREATE TABLE customers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

-- SQL Server: IDENTITY(seed, increment)
CREATE TABLE customers (
  id BIGINT IDENTITY(1, 1) PRIMARY KEY,
  name NVARCHAR(100) NOT NULL
);
SQLite PostgreSQL MySQL / MariaDB SQL Server Oracle 12c+
Declare INTEGER PRIMARY KEY GENERATED โ€ฆ AS IDENTITY, serial AUTO_INCREMENT IDENTITY(1, 1) GENERATED โ€ฆ AS IDENTITY
Get new id last_insert_rowid(), RETURNING RETURNING id LAST_INSERT_ID() SCOPE_IDENTITY(), OUTPUT inserted.id RETURNING id INTO โ€ฆ
Explicit ids allowed? Yes BY DEFAULT: yes; ALWAYS: only with OVERRIDING SYSTEM VALUE Yes Only with SET IDENTITY_INSERT โ€ฆ ON BY DEFAULT: yes
Reuses ids? Can (unless AUTOINCREMENT) No No (InnoDB, MySQL 8+) No No

Changing the start value and the step. SQL Server's IDENTITY(seed, increment) sets both: IDENTITY(1000, 5) yields 1000, 1005, 1010. PostgreSQL and Oracle use GENERATED ... AS IDENTITY (START WITH 1000 INCREMENT BY 5). MySQL takes the start from the table option AUTO_INCREMENT = 1000 (ALTER TABLE customers AUTO_INCREMENT = 1000;), while its step is a server or session variable, auto_increment_increment, rather than a column property.

PostgreSQL's GENERATED ALWAYS rejects hand-written ids, which protects you from accidentally inserting a value that the generator will hand out again later. GENERATED BY DEFAULT lets you supply one, which is handy when importing data, but then you must move the generator past the imported values yourself.

Gaps are normal. In PostgreSQL, MySQL and SQL Server, a number is consumed as soon as an insert asks for it, even if that insert then fails or its transaction rolls back. A server restart can also skip a block of values. Never assume ids are contiguous, and never use them to count rows or to tell which row came "first" across concurrent sessions.

Sequences

A sequence is a standalone database object that hands out increasing numbers. PostgreSQL's serial and identity columns are built on sequences, and Oracle relied on them for decades. You can also use a sequence directly, for example to share one number series across several tables or to generate invoice numbers:

SQL
-- PostgreSQL
CREATE SEQUENCE invoice_numbers START WITH 1000 INCREMENT BY 1;

SELECT nextval('invoice_numbers');   -- 1000
SELECT nextval('invoice_numbers');   -- 1001
SELECT currval('invoice_numbers');   -- 1001 (last value in this session)

INSERT INTO invoices (number, customer_id)
VALUES (nextval('invoice_numbers'), 42);

SQL Server (2012+) and Oracle use NEXT VALUE FOR invoice_numbers and invoice_numbers.NEXTVAL. SQLite has no sequences, but a one-row counter table and UPDATE โ€ฆ RETURNING behave the same way:

SQL A sequence in SQLite

Press Run (or Ctrl + Enter) to see the result here.

The two statements return 1000 and 1001. Because the increment and the read happen in a single statement, two connections can never receive the same number.

UUID keys

A UUID (universally unique identifier) is a 128-bit value usually written as 36 characters of hex, like 0b9e5c62-3f1d-4c8a-9f4e-6a2d1b7c8e90. Random UUIDs are so unlikely to collide that any computer can generate one without asking the database. That gives them real advantages:

  • Generated anywhere: a mobile app can create a record offline and sync it later.
  • Safe to merge: rows from several databases or shards never clash.
  • Not guessable: /orders/42 invites someone to try /orders/43; a UUID doesn't. (You still need real access checks.)

And real costs:

  • Size: 16 bytes as binary, 36 as text, versus 8 for a BIGINT. Every index and foreign key that copies the key grows too.
  • Random insert order: fully random UUIDs (version 4) land all over the primary-key index, which slows inserts on large tables. UUID version 7 fixes this by starting with a timestamp, so new values sort after old ones.
  • Readability: "order 1042" is easier to say on the phone.

Support differs: PostgreSQL has a native uuid type and gen_random_uuid() (version 4, PostgreSQL 13+), plus uuidv7() from PostgreSQL 18. MySQL has UUID() (version 1) and stores UUIDs best as BINARY(16). SQL Server has UNIQUEIDENTIFIER with NEWID() and NEWSEQUENTIALID(). SQLite has no built-in UUID function, but you can build a random version 4 UUID from randomblob():

SQL Random UUIDs in SQLite

Press Run (or Ctrl + Enter) to see the result here.

Run it a few times: the ids change every run, but each one has 36 characters and the 4 that marks version 4. In application code you'd normally use your language's UUID library instead.

A popular compromise is to keep a compact integer primary key for joins and add a UNIQUE UUID column (often called public_id) for URLs and APIs. You get small, fast foreign keys inside the database and unguessable identifiers outside it.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In SQLite, a table has id INTEGER PRIMARY KEY and rows with ids 1, 2 and 7. What id does the next insert without an id get?

    SQLite picks one more than the largest id currently in the table, so gaps below the maximum are not filled.
  2. 2. What does SELECT id FROM t; show after CREATE TABLE t (id INT PRIMARY KEY, name TEXT); INSERT INTO t (name) VALUES ('x'); in SQLite?

    Only INTEGER PRIMARY KEY is an alias for rowid. INT PRIMARY KEY is an ordinary column, and SQLite allows NULL in it, so the id is NULL.
  3. 3. What does adding AUTOINCREMENT to an SQLite INTEGER PRIMARY KEY change?

    INTEGER PRIMARY KEY is already auto-numbered. AUTOINCREMENT stores the highest id ever used in sqlite_sequence so deleted ids are never handed out again. Gaps can still occur.
  4. 4. Why can a PostgreSQL identity column have gaps such as 1, 2, 5?

    A sequence value is used up as soon as it's requested and is not returned on rollback. Gaps are normal and harmless.
  5. 5. What is the main advantage of UUID version 7 over version 4 as a primary key?

    Random v4 UUIDs scatter inserts across the index. v7 UUIDs begin with the time, so inserts append near the end like integer ids.

Summary

  • Every ordinary SQLite table has a hidden 64-bit rowid; INTEGER PRIMARY KEY (exactly that spelling) makes your column an alias for it and fills it in automatically.
  • New SQLite ids are "largest id plus one", so deleted top ids can be reused; AUTOINCREMENT prevents that using sqlite_sequence.
  • Get the new id with last_insert_rowid() or, better, RETURNING.
  • Other databases: GENERATED โ€ฆ AS IDENTITY (standard, PostgreSQL, Oracle), serial (older PostgreSQL), AUTO_INCREMENT (MySQL), IDENTITY(1, 1) (SQL Server).
  • Sequences are standalone number generators; emulate one in SQLite with a counter table and UPDATE โ€ฆ RETURNING.
  • Ids can have gaps; never rely on them being contiguous.
  • UUIDs can be generated anywhere and aren't guessable, but they're bigger; prefer version 7 for keys, or pair an integer key with a unique public UUID.

Next: Constraints, where you'll make the database reject bad data for you.