Here we will learn SQLite auto increment with examples and how to use SQLite auto increment property with column to generate unique values while inserting data with example.
In SQLite if we set auto increment property on column it will generate sequential unique numeric values automatically whenever we insert a new record or row in the table.
Generally, the auto increment property in SQLite can only work with numeric data types and its very useful to use with primary key constraints because primary key will always allow only unique values.
Suppose, in SQLite when we create a table without specifying AUTOINCREMENT option on a column then SQLite automatically creates the ROWID column to identify table rows uniquely within the table.
Now we will see with examples of creating table in SQLite with and without AutoIncrement columns. Following is the query which we used to create Persons table without AutoIncrement column.
CREATE TABLE Persons
(first_name TEXT,
last_name TEXT);
If you observe above query we created a table without defining any AutoIncrement property on columns. Now use following queries to insert data in a table.
INSERT INTO Persons
VALUES('Vinay','Jariwala');
INSERT INTO Persons
VALUES('Shweta','Rana');
Once we done with the insertion of data use a following SELECT statement to check Persons table.
SELECT rowid,first_name,last_name FROM Persons;
rowid first_name last_name
---------- ---------- ----------
1 Vinay Jariwala
2 Shweta Rana
If you observe the above result SQLite automatically created an implicit column rowid and assigns an integer value whenever we insert a new row.
Suppose if we create a table with INTEGER PRIMARY KEY but without specifying AUTOINCREMENT then this column will point to the row_id column.
Following is the example of creating a table with Primary Key.
DROP TABLE Persons;
CREATE TABLE Persons
(id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT);
If you observe the above statement we are dropping old table Persons and recreating same Persons table with INTEGER PRIMARY KEY. Here the id column will act as a row_id.
Now, if we insert a NULL value in the id column, it won’t give any error and it will assign an integer value which is one greater than the largest rowid in the table because it’s the primary key of table. In case if the table contains no data means then it will start from 1.
In SQLite maximum rowid value is 9223372036854775807. In case if records reach this maximum limit then SQLite search for unused integer values and insert the new records in that values. If SQLite fails to find integer value then it will throw an error.
Following is the example of inserting a new row with maximum rowid value.
INSERT INTO Persons
(id,first_name,last_name)
VALUES(9223372036854775807,'Vinay','Jariwala');
Now we will try to insert a new row without specifying id column value using following SQLite statement.
INSERT INTO Persons
(first_name,last_name)
VALUES('Shweta','Jariwala');
Now we will check the data of Persons table with SELECT statement.
sqlite> SELECT * FROM Persons;
id first_name last_name
------------------- ---------- ----------
1017135683236837057 Vinay Jariwala
9223372036854775807 Shweta Jariwala
Now we will see how to create table with INTEGER PRIMARY KEY AUTOINCREMENT for that use following query to drop existing Persons table and recreate it with AUTOINCREMENT property on a PRIMARY KEY column.
DROP TABLE Persons;
CREATE TABLE Persons
(id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT);
In SQLite the starting value for AUTOINCREMENT is 1 and it will increment by 1 for each new record.
Now we will insert new records in Persons table for that use following queries.
INSERT INTO Persons(first_name,last_name)
VALUES('Shweta','Rana');
Now we will check the data of Persons table with SELECT statement.
sqlite> SELECT * FROM Persons;
id first_name last_name
---------- ---------- ----------
1 Shweta Rana
As we know that SQLite keeps track of rowid in the sqlite_sequence catalog and we can also check the values of sqlite_catalog using the following query.
sqlite> SELECT * FROM sqlite_sequence;
name seq
----------- ----------
dept_master 5
emp_master 9
Employee 4
Persons 1
As we know AUTOINCREMENT property will start sequence with value “1” in case if we want to set custom starting value then we need to update the sequence value for that use following query.
UPDATE sqlite_sequence SET seq = 100 WHERE NAME = 'Persons';
Once we update the sequence value again we will check the data of the sqlite_sequence catalog using the following SELECT statement.
sqlite> SELECT * FROM sqlite_sequence;
name seq
----------- ----------
dept_master 5
emp_master 9
Employee 4
Persons 100
Now, we will try to insert a new record in Persons table using the following statements.
INSERT INTO Persons(first_name,last_name)
VALUES('Shweta','Rana');
Once we done with insertion we will check the records using SELECT statement.
sqlite> SELECT * FROM Persons;
id first_name last_name
---------- ---------- ----------
1 Shweta Rana
101 Shweta Rana
Here, you can see that sequence is reset and started from 100. So here we got the values of id as 101.
Generally, in SQLite the AUTOINCREMENT is not necessary because the same functionality can be achieved by ROWID and it will impose extra CPU, memory, disk space and disk I/O overhead.
This is how we can use SQLite AUTOINCREMENT property in our applications based on our requirements.