SQLite has an interesting way of handling auto-increment columns. By auto-incrementing columns, I mean columns that increment automatically whenever new data is inserted.

This is similar to an IDENTITY column in SQL Server or an AUTO_INCREMENT column in MySQL.

This article explains how to create AUTOINCREMENT columns in SQLite.

Automatically Create an Auto-Increment Column

By default, when you define a column as INTEGER PRIMARY KEY, it will auto-increment whenever you insert NULL into that column.

Example:

CREATE TABLE Cats( 
    CatId INTEGER PRIMARY KEY, 
    CatName
);

In this table, the CatId column is an autoincrement column. This is because it has been defined using INTEGER PRIMARY KEY.

Now, when I insert NULL into that column, the CatId column auto-increments:

INSERT INTO Cats VALUES 
    ( NULL, 'Brush' ),
    ( NULL, 'Scarcat' ),
    ( NULL, 'Flutter' );

SELECT * FROM Cats;

Result:

CatId       CatName   
----------  ----------
1           Brush     
2           Scarcat   
3           Flutter   

It’s important to note that you can override the AUTOINCREMENT value by inserting your own value. In other words, AUTOINCREMENT only inserts a value if you don’t.

The way it works is, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table. If the table is empty, the value will be 1.

If the largest value of the column is the largest possible integer (9223372036854775807), then SQLite will choose an unused key at random. This typically means that it will reuse old keys that were previously deleted. If an unused key can’t be found, the INSERT operation fails with an SQLITE_FULL error.

Basically what this means is, if you allow DELETE operations in the table, then there’s no guarantee that all rows will be in order. There’s a possibility that some rows will have a higher value than rows inserted at a later date.

Therefore, in such cases, you can’t rely on this column if you need to order the table in ascending or descending order, based on the order of which the rows were inserted.

Fortunately, if this is a problem for you, there is a solution: The AUTOINCREMENT keyword.

Use the AUTOINCREMENT Keyword

Alternatively, you can choose to explicitly set the column to auto-increment by using the AUTOINCREMENT keyword.

One benefit of using this method is that it guarantees that all rows will be in ascending order. This is because it doesn’t reuse previously deleted keys. Each key will always be one more than the largest key that has ever existed in that table. If the largest possible key has previously existed in that table, then it won’t try to use previously deleted keys. The INSERT will fail with an SQLITE_FULL error code.

The downside of using the AUTOINCREMENT keyword is that it uses extra CPU, memory, disk space, and disk I/O overhead.

Here’s an example of creating an auto-incrementing column with the AUTOINCREMENT keyword:

CREATE TABLE Dogs( 
    DogId INTEGER PRIMARY KEY AUTOINCREMENT, 
    DogName
);

Now insert data and select it:

INSERT INTO Dogs VALUES 
    ( NULL, 'Yelp' ),
    ( NULL, 'Woofer' ),
    ( NULL, 'Fluff' );

SELECT * FROM Dogs;

Result:

DogId       DogName   
----------  ----------
1           Yelp      
2           Woofer    
3           Fluff     

If I were to delete Fluff from this table, then insert a new row (using NULL as the DogId), the new DogId would be 4. In other words, it would not reuse 3.

If the column had been created without the AUTOINCREMENT keyword, then the next row would reuse the DogId of 3.

If I were to insert a DogId of 9223372036854775807 (the largest possible integer), I would receive the following error upon the next insert that specifies NULL for that column:

Error: database or disk is full

However, I could explicitly insert a value that is lower than 9223372036854775807, as long as that value isn’t already being used by another row, and the INSERT operation should succeed without the above error.

Basically, once you reach 9223372036854775807, the auto-increment will no longer work.

Columns defined without the AUTOINCREMENT keyword don’t have this issue. They will automatically go back and try to find an unused integer to use instead. However, if all integers have been used (i.e. the table actually contains 9223372036854775807 rows), then even those columns will result in the above error.