AUTOINCREMENT

Keeping in mind to increase the id number can be annoying. SQL provides the AUTOINCREMENT option that can help us maintain that value. However in SQLite it is not necessary to use and it isn’t even recommended.

In any case let’s see an example:

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

INSERT INTO person (name, email) VALUES ('Joe', 'joe@example.com');
INSERT INTO person (name, email) VALUES ('Jane', 'jane@example.com');
INSERT INTO person (name, email) VALUES ('Mary', 'mary@example.com');
INSERT INTO person (name, email) VALUES ('Peter', 'peter@example.com');

SELECT * from person;

.schema

$ sqlite3 < examples/autoincrement.sql

1|Joe|joe@example.com
2|Jane|jane@example.com
3|Mary|mary@example.com
4|Peter|peter@example.com
CREATE TABLE person (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT
);
CREATE TABLE sqlite_sequence(name,seq);