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.

WARNING

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:

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    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) ↩︎