# 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
- 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
# 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.
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.