In SQLite, an AUTOINCREMENT column is one that uses an automatically incremented value for each row that’s inserted into the table.
There are a couple of ways you can create an AUTOINCREMENT column:
- You can create it implicitly when you define the column as
INTEGER PRIMARY KEY. - You can create it explicitly with the
AUTOINCREMENTkeyword. One downside of this method is that it uses extra CPU, memory, disk space, and disk I/O overhead.
Both methods cause the column to use an incrementing value each time a new row is inserted with NULL in that column.
However, there are some subtle differences between how each method works.
Without the AUTOINCREMENT Keyword
When you declare a column as INTEGER PRIMARY KEY, it will automatically increment. Therefore, you don’t actually need to use the AUTOINCREMENT keyword to have a column that uses an automatically incrementing value for each row.
When you do this, any NULL values are converted into the the current ROWID. In other words, if you insert NULL into that column, it will be converted to the current ROWID.
Actually, the way it works is that the column becomes an alias for the ROWID. You can access the ROWID value by using any of four names; the column name, ROWID, _ROWID_, or OID.
One benefit of omitting the AUTOINCREMENT keyword is that it reduces CPU, memory, disk space, and disk I/O overhead.
An important downside however, is that you can’t guarantee that all rows will be incremented in ascending order. This is due to the way auto-incrementing works when omitting the AUTOINCREMENT keyword vs using that keyword.
When you omit the AUTOINCREMENT keyword, once ROWID is equal to the largest possible integer (9223372036854775807), SQLite will try to find an unused positive ROWID at random.
However, as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID, this method will generate monotonically increasing unique ROWIDs.
With the AUTOINCREMENT Keyword
You can also create auto-incrementing columns explicitly. To do this, use the AUTOINCREMENT keyword.
When you use this method, a slightly different algorithm is used to determine the auto-incremented value.
When you use the AUTOINCREMENT keyword, the ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table.
In other words, it won’t go back and reuse previously deleted ROWID values. Once the largest possible ROWID has been inserted, no new inserts are allowed. Any attempt to insert a new row will fail with an SQLITE_FULL error.
Therefore using this method guarantees that the ROWIDs are monotonically increasing. In other words, you can rely on this method for having ROWIDs in ascending order.
This doesn’t meant that the values will always increment by 1 though. It just means that they will never decrease.
Example
Here’s an example to demonstrate the difference between implicitly and explicitly defining an auto-increment column.
CREATE TABLE Cats(
CatId INTEGER PRIMARY KEY,
CatName
);
CREATE TABLE Dogs(
DogId INTEGER PRIMARY KEY AUTOINCREMENT,
DogName
);
INSERT INTO Cats VALUES
( NULL, 'Brush' ),
( NULL, 'Scarcat' ),
( NULL, 'Flutter' );
INSERT INTO Dogs VALUES
( NULL, 'Yelp' ),
( NULL, 'Woofer' ),
( NULL, 'Fluff' );
SELECT * FROM Cats;
SELECT * FROM Dogs;Initial result:
CatId CatName ---------- ---------- 1 Brush 2 Scarcat 3 Flutter DogId DogName ---------- ---------- 1 Yelp 2 Woofer 3 Fluff
Now let’s delete the last row in each table, insert the rows again, then select the result:
DELETE FROM Cats WHERE CatId = 3;
DELETE FROM Dogs WHERE DogId = 3;
INSERT INTO Cats VALUES ( NULL, 'New Flutter' );
INSERT INTO Dogs VALUES ( NULL, 'New Fluff' );
SELECT * FROM Cats;
SELECT * FROM Dogs;Result:
CatId CatName ---------- ---------- 1 Brush 2 Scarcat 3 New Flutter DogId DogName ---------- ---------- 1 Yelp 2 Woofer 4 New Fluff
To recap, the Cats table was created without the AUTOINCREMENT keyword, and the Dogs table was created with the AUTOINCREMENT keyword.
After deleting the last row from the Cats table, the next INSERT operation resulted in the same ROWID being reused for that row.
However, the Dogs table was different. It was created with the AUTOINCREMENT keyword and therefore it can’t reuse the previous value. It simply increments to the next value, leaving a gap in the numbering.
Maximum ROWID
We can take the previous example a step further and insert a new row by explicitly using the maximum ROWID possible.
INSERT INTO Cats VALUES ( 9223372036854775807, 'Magnus' );
INSERT INTO Dogs VALUES ( 9223372036854775807, 'Maximus' );
SELECT * FROM Cats;
SELECT * FROM Dogs;Result:
CatId CatName -------------------- -------------------- 1 Brush 2 Scarcat 3 New Flutter 9223372036854775807 Magnus DogId DogName -------------------- -------------------- 1 Yelp 2 Woofer 4 New Fluff 9223372036854775807 Maximus
OK, so both tables use the largest possible integer as their largest ROWID values.
Let’s see what happens when I try to insert a new row into each table (without explicitly specifying a value for the auto-incrementing columns).
Insert into the Cats table:
INSERT INTO Cats VALUES ( NULL, 'Scratchy' );
SELECT * FROM Cats;Result:
CatId CatName -------------------- -------------------- 1 Brush 2 Scarcat 3 New Flutter 267244677462397326 Scratchy 9223372036854775807 Magnus
So the Cats table was successful. However, notice that the new auto-incrementing value is lower than the previous value (it has no other option).
Without having any other column to record the date/time that the row was inserted/updated, one might incorrectly assume that Magnus was inserted after Scratchy.
Now let’s try to insert a new row to the Dogs table:
INSERT INTO Dogs VALUES ( NULL, 'Lickable' );Result:
Error: database or disk is full
So we get a different result to the Cats table.
I got this error because the largest possible ROWID is already being used in the table, and because this table was created with the AUTOINCREMENT keyword, it will not go back and search for an unused ROWID value.
SELECT * FROM Dogs;Result:
DogId DogName -------------------- -------------------- 1 Yelp 2 Woofer 4 New Fluff 9223372036854775807 Maximus
Furthermore, I will continue to get this error, even if I delete Maximus from the table (i.e. the dog with the maximum ROWID value).
Let’s try it:
DELETE FROM Dogs WHERE DogId = 9223372036854775807;
INSERT INTO Dogs VALUES ( NULL, 'Lickable' );Result:
Error: database or disk is full
So not only do we get an error, but I’ve also deleted Maximus:
SELECT * FROM Dogs;Result:
DogId DogName -------------------- -------------------- 1 Yelp 2 Woofer 4 New Fluff
It’s important to note that this will happen even if I change the ROWID value to a lower value. The fact that I’ve already used a ROWID of 9223372036854775807 means that AUTOINCREMENT will no longer increment automatically.
This is because AUTOINCREMENT only uses values that are at least one higher than any value that has ever before existed in that same table.
From now on, if I wanted to continue to insert values into this column, I would need to explicitly insert the value. This assumes that I don’t already have 9223372036854775807 rows in the table.
Let’s reinsert Maximus with a lower ROWID and try again:
INSERT INTO Dogs VALUES ( 5, 'Maximus' );
SELECT * FROM Dogs;Result:
DogId DogName -------------------- -------------------- 1 Yelp 2 Woofer 4 New Fluff 5 Maximus
Now let’s try to insert Lickable once again, using AUTOINCREMENT:
INSERT INTO Dogs VALUES ( NULL, 'Lickable' );Result:
Error: database or disk is full
So even though I’ve deleted the row that contains the maximum ROWID value of 9223372036854775807, it still prevents me from auto-incrementing the column.
This is exactly how it was designed. The maximum ROWID has previously been in this table and so AUTOINCREMENT will not automatically increment again in that table.
The only way to add a new row to that table is to manually insert the ROWID value.
INSERT INTO Dogs VALUES (6, 'Lickable');
SELECT * FROM Dogs;Result:
DogId DogName -------------------- -------------------- 1 Yelp 2 Woofer 4 New Fluff 5 Maximus 6 Lickable