The ROWID column gets automatically incremented as you add data to the table. For example, consider the query below to create and add data.
"name" TEXT,
"role" TEXT
);
Next, add data to the created table above using the query provided below:
INSERT INTO "main"."demo" ("name", "role") VALUES ('Terhi Antonije', 'Hardware Engineer');
Now:
If we perform a SELECT query on the table, we will get a table as shown below.
As seen from the output above, SQLite automatically creates a field (ROWID) that gets automatically incremented as data gets added to the table.
However, you can explicitly specify the row id when creating a table using the AUTOINCREMENT keyword. Let us discuss how to do this in this tutorial.
How to Use AUTOINCREMENT
To start using the AUTOINCREMENT keyword, the specified field should only accept INTEGER values. The general syntax for AUTOINCREMENT keyword is as:
"Field1" INTEGER,
PRIMARY KEY("Field1" AUTOINCREMENT)
);
For example, to consider the table below:
"No" INTEGER,
"Name" TEXT,
"Age" INTEGER,
"Language" TEXT,
PRIMARY KEY("No" AUTOINCREMENT)
);
Let’sadd data into the table using the Query provided below:
INSERT INTO "main"."users" ("No", "Name", "Age", "Language") VALUES ('2', 'Glaucia Martha', '30', 'JavaScript, Ruby on Rails');
INSERT INTO "main"."users" ("No", "Name", "Age", "Language") VALUES ('3', 'Tindra Alona', '25', 'C++, Bash');
INSERT INTO "main"."users" ("No", "Name", "Age", "Language") VALUES ('4', 'Yakau Kelley', '33', 'Java, Mathematica, Python');
Once you execute the above query, you will get a table with the data shown below:
You will note that the values of the No field get incremented automatically. You can also specify the position or location of the data you wish to add. For example, to add data at No (id) 1000, specify the value as shown in the query below:
Now, if you query the data in the above table, you will see a table as shown below:
As you can see from the output above, the data we inserted is located at position 1000 as specified. The maximum value can be at the value of 9223372036854775807. If you try to add more data while it has reached the maximum value, SQLite will look for an unused value and insert data at that location.
NOTE: SQLite recommends using AUTOINCREMENT as you can learn more in the resource provided below
https://sqlite.org/autoinc.html
Conclusion
This tutorial has covered how to use the SQL autoincrement keyword on various fields. Before deciding when to use the AUTOINCREMENT keyword, ensure you read its documentation, features, and limitations.
