SQLite | Tables | AUTOINCREMENT Allocation Rules

AUTOINCREMENT allocation rules

An INTEGER PRIMARY KEY normally receives one more than the largest value currently stored, so a deleted highest value may be reused. Adding AUTOINCREMENT instead uses one more than the greatest value ever allocated.

CREATE TABLE table_name (
  column_name INTEGER PRIMARY KEY AUTOINCREMENT, ...
);

Without AUTOINCREMENT:

create table user (id integer primary key, name text);
insert into user (name) values ('devkuma');
insert into user (name) values ('kimkc');
insert into user (name) values ('araikuma');
delete from user where id = 3;
insert into user (name) values ('happykuma');

The last row receives ID 3 again because the current maximum is 2.

With AUTOINCREMENT:

create table user2 (id integer primary key autoincrement, name text);
insert into user2 (name) values ('devkuma');
insert into user2 (name) values ('kimkc');
insert into user2 (name) values ('araikuma');
delete from user2 where id = 3;
insert into user2 (name) values ('happykuma');

The last row receives ID 4 because 3 was previously allocated. This prevents automatic reuse of earlier values.

Check the greatest allocated value

SQLite records AUTOINCREMENT state in sqlite_sequence:

sqlite> .schema sqlite_sequence
CREATE TABLE sqlite_sequence(name,seq);

name is the table name and seq is its greatest allocated value.

select * from sqlite_sequence where name = 'user2';

user2|4