# Autoincrementing IDs

In relational databases, it is quite common to provide each row with a unique identifier, as we have learned.

Often to make your life easier, we use an auto-incrementing row. That means that the first row we add to the database will get a value of 1 for its ID. The second row will get a value of 2, and so on.

You may have noticed already that when we created a column which was INTEGER PRIMARY KEY in SQLite, if we create a new row but don't provide that column with a value, it will generate one in this way.

In other RDBMS's, such as PostgreSQL, we will have to specify this behaviour ourselves.

# Behind the scenes

What's really happening behind the scenes is:

  • All SQLite tables are created with a hidden column that is an auto-incrementing integer, unique in the table. This column is called rowid.
  • When you create a column that is INTEGER PRIMARY KEY, that column is just an alias for rowid. So nothing new is being created, it just means that you have a more visible way of accessing rowid.

# A minor caveat

In most database systems, when you define an auto-incrementing column, it only ever goes up in value.

This means that if you delete a row, the value of its unique auto-incrementing field will not be re-used.

However, in SQLite the rowid value may be re-used if it is deleted.

If you wish to prevent that behaviour, then you can define a field to be INTEGER PRIMARY KEY AUTOINCREMENT. That way the id-assignment algorithm will change to prevent reuse of old values.


The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.[1]

Here's what that might look like:

    first_name TEXT,
    surname TEXT

So just to recap, this means that the hidden rowid and the visible id columns will have the same values, but those values will never be reused if a row is ever deleted.

  1. SQLite Autoincrement (official documentation) (opens new window) ↩ī¸Ž