Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 33 ยท Types and Tables

Tables, rowid and INTEGER PRIMARY KEY

Learn how SQLite stores rows: the hidden rowid, INTEGER PRIMARY KEY as an alias, AUTOINCREMENT versus plain keys, WITHOUT ROWID tables and last_insert_rowid().

Under the surface, every ordinary SQLite table is a sorted tree of rows, and the key of that tree is a 64-bit integer called the rowid. Once you understand it, a lot of SQLite behavior makes sense: why INTEGER PRIMARY KEY is special, why lookups by id are so fast, and why AUTOINCREMENT is almost never needed.

You met the rowid briefly in Your First Database. Here we go through the rules in detail, including how numbers are chosen, what happens after deletes, and the alternative table layout called WITHOUT ROWID.

How SQLite identifies a row

rowid A hidden 64-bit integer on every ordinary table.
INTEGER PRIMARY KEY Becomes another name for the rowid.
AUTOINCREMENT Optional extra rule, never reuse an id.
WITHOUT ROWID A table keyed by your own primary key.
1 / 2

The rowid

When you create a table the normal way, SQLite adds a hidden column named rowid (also reachable as _rowid_ or oid). Rows are stored in rowid order, so finding a row by rowid is a quick tree search.

SQL A table with no declared key

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

After the first insert the rowids are 1, 2 and 3. After deleting banana (rowid 2) and inserting date, date gets rowid 4, not 2: SQLite uses one more than the largest rowid currently in the table. That rule will matter shortly.

You can also choose the rowid yourself by giving it in the insert:

SQL Choosing a rowid

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

Apple is 100, and banana automatically gets 101. Inserting a rowid that already exists causes a constraint error.

INTEGER PRIMARY KEY is an alias

If a column is declared with the exact type name INTEGER and is the PRIMARY KEY, that column is the rowid. The data is stored only once, and id and rowid always show the same number.

SQL id is the rowid

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

Ada and Grace get 1 and 2, Alan is given 50, and Katherine continues at 51.

A few details trip people up:

  • The match is on the exact words INTEGER PRIMARY KEY. INT PRIMARY KEY and BIGINT PRIMARY KEY are ordinary indexed columns with INTEGER affinity, and they do not become the rowid. In those, an omitted value is stored as NULL.
  • A rowid column cannot hold anything but an integer. Inserting 'abc' fails with a "datatype mismatch" error, even without STRICT.
  • Descending order, as in id INTEGER PRIMARY KEY DESC, also stops the alias. Keep the declaration simple.
SQL INT is not the same as INTEGER

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

Table a has id 1. In table b, the id is NULL and the hidden rowid is 1. That is legal in SQLite (it is a historical quirk: primary keys may hold NULL unless you add NOT NULL). Always declare INTEGER PRIMARY KEY exactly if you want automatic ids.

AUTOINCREMENT versus the default

Plain INTEGER PRIMARY KEY picks "max rowid + 1". If you delete the newest row, its number can be handed out again. Add AUTOINCREMENT and SQLite instead remembers the highest number it has ever used (in an internal table named sqlite_sequence) and never reuses one.

SQL Reusing ids or not

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

In plain, d takes the freed id 3. In auto, d becomes 4, because 3 was used once and is gone for good. The sqlite_sequence table shows the highest number used so far for each AUTOINCREMENT table: 4 for auto.

So when should you use it? Only when reusing an id would be harmful, for example when ids appear in external logs, URLs or other systems and a stale reference could point at a new row. For most tables, plain INTEGER PRIMARY KEY is simpler and slightly faster, since AUTOINCREMENT needs an extra table lookup on every insert. Both guarantee uniqueness among the rows that exist now.

One more difference: with plain rowids, if the largest value ever reaches 9223372036854775807, SQLite searches for unused numbers at random. With AUTOINCREMENT, inserts then fail with an error. You will not hit this in practice.

Getting the new id back

last_insert_rowid() gives the rowid of the latest insert on the current connection. Use it to link rows created together, for instance an order and its first line item:

SQL Using last_insert_rowid()

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

Each item is attached to the order created just before it. The value is per connection, so another connection's inserts never change it. Triggers that insert rows do not change the value as seen by the statement that fired them. In real code, prefer the driver's own method or RETURNING (see UPSERT and RETURNING).

Composite keys on rowid tables

A table can have a primary key made of several columns, or a text primary key. It is still a rowid table: the rowid stays as the internal key, and SQLite builds a separate unique index for your declared key.

SQL Text and composite keys

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

Both tables got an automatic index, shown in the second result with names starting sqlite_autoindex_. Storing the key twice (in the table and in the index) is the cost of rowid tables, and the reason WITHOUT ROWID exists.

WITHOUT ROWID tables

Adding WITHOUT ROWID at the end of CREATE TABLE tells SQLite to store the table directly in the order of its primary key, with no hidden rowid.

SQL A WITHOUT ROWID table

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

The first query lists the settings sorted by key. The second fails with "no such column: rowid": a WITHOUT ROWID table has none. The run stops at that point, which is expected.

Such tables suit these cases:

  • The table has a composite or text primary key and few other columns, so the separate index of a rowid table would be wasteful.
  • You mostly look rows up by that key.

They are a poor choice for tables with very large rows or when you rely on last_insert_rowid(). A WITHOUT ROWID table must declare a PRIMARY KEY, and its key columns can never be NULL. For most tables the ordinary layout is the right default.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which declaration makes id an alias for the rowid?

    Only the exact type name INTEGER together with PRIMARY KEY creates the alias. INT and BIGINT keys are separate indexed columns.
  2. 2. A plain INTEGER PRIMARY KEY table has rows 1, 2, 3. You delete row 3 and insert a new row. Which id does the new row get?

    Without AUTOINCREMENT SQLite uses the largest existing rowid plus one, so the freed number 3 is reused.
  3. 3. What does AUTOINCREMENT add?

    Automatic numbering already works without it. AUTOINCREMENT only prevents reuse of ids, at a small cost.
  4. 4. What happens when you run SELECT rowid FROM t on a WITHOUT ROWID table?

    WITHOUT ROWID tables are stored by primary key and have no hidden rowid.
  5. 5. Which value does last_insert_rowid() report?

    It is tracked per connection and updated by inserts.

Summary

  • Every ordinary table has a hidden 64-bit rowid and is stored in rowid order; lookups by rowid are fast.
  • A column declared exactly INTEGER PRIMARY KEY is the rowid and numbers itself as "largest existing + 1".
  • AUTOINCREMENT also records the highest id ever used so ids are never reused; it costs a little speed and is rarely needed.
  • INT or BIGINT primary keys are ordinary columns and may even hold NULL unless declared NOT NULL.
  • WITHOUT ROWID tables are stored by primary key, which suits compact tables with text or composite keys.
  • last_insert_rowid() returns the latest inserted rowid on the current connection.

Next: Constraints and STRICT Tables makes SQLite refuse bad data.