AUTOINCREMENT vs No AUTOINCREMENT in SQLite

Question

I put and did not put AUTOINCREMENT for id in person table as shown below:

CREATE TABLE person (
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  name TEXT            # ↑↑↑↑↑↑↑↑↑↑↑↑↑
);

CREATE TABLE person (
  id INTEGER PRIMARY KEY, # No AUTOINCREMENT
  name TEXT
);

Then, I added 3 rows to person table as shown below:

INSERT INTO person (name) 
VALUES ('John'), ('David'), ('Lisa');

Finally, I got the exactly same 3 rows from person table both incrementing the number for id by one as shown below:

.headers on  
.mode box
SELECT * FROM person;
┌────┬───────┐
│ id │ name  │
├────┼───────┤
│ 1  │ John  │
│ 2  │ David │
│ 3  │ Lisa  │
└────┴───────┘

So, what is the difference between AUTOINCREMENT and no AUTOINCREMENT for id in person table?

Answer 1

AUTOINCREMENT can avoid to reuse the deleted numbers unless explicitly specifying them while no AUTOINCREMENT cannot according to the doc.

For example, you put AUTOINCREMENT for id in person table and it has 3 rows as shown below:

CREATE TABLE person (
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  name TEXT           -- ↑↑↑↑↑↑↑↑↑↑↑↑↑
);

SELECT * FROM person;
┌────┬───────┐
│ id │ name  │
├────┼───────┤
│ 1  │ John  │
│ 2  │ David │
│ 3  │ Lisa  │
└────┴───────┘

Then, you delete the row where id is 3, then get 2 rows from person table as shown below:

DELETE FROM person where id = 3;

SELECT * FROM person;
┌────┬───────┐
│ id │ name  │
├────┼───────┤
│ 1  │ John  │
│ 2  │ David │
└────┴───────┘

Now, you add one row to person table, then 4 is added to id as shown below:

INSERT INTO person (name) values ('Anna');

SELECT * FROM person;
┌────┬───────┐
│ id │ name  │
├────┼───────┤
│ 1  │ John  │
│ 2  │ David │
│ 4  │ Anna  │ <- Here
└────┴───────┘

In addition, if you add one row to person table by explicitly setting 3 to id, then 3 is added to id as shown below:

INSERT INTO person (id, name) values (3, 'Anna');
                  # ↑↑              # ↑
SELECT * FROM person;
┌────┬───────┐
│ id │ name  │
├────┼───────┤
│ 1  │ John  │
│ 2  │ David │
│ 3  │ Anna  │ <- Here
└────┴───────┘

Next for example, you do not put AUTOINCREMENT for id in person table and it has 3 rows as shown below:

CREATE TABLE person (
  id INTEGER PRIMARY KEY, -- No AUTOINCREMENT
  name TEXT
);

SELECT * FROM person;
┌────┬───────┐
│ id │ name  │
├────┼───────┤
│ 1  │ John  │
│ 2  │ David │
│ 3  │ Lisa  │
└────┴───────┘

Then, you delete the row where id is 3, then get 2 rows from person table as shown below:

DELETE FROM person where id = 3;

SELECT * FROM person;
┌────┬───────┐
│ id │ name  │
├────┼───────┤
│ 1  │ John  │
│ 2  │ David │
└────┴───────┘

Now, you add one row to person table, then 3 is added to id as shown below:

INSERT INTO person (name) values ('Anna');

SELECT * FROM person;
┌────┬───────┐
│ id │ name  │
├────┼───────┤
│ 1  │ John  │
│ 2  │ David │
│ 3  │ Anna  │ <- Here
└────┴───────┘