SQLite

How to use SQLite Autoincrement

SQLite supports the AUTOINCREMENT keyword that auto increments a value of the specified field in a database table. By default, when you create a database table, you will get a 64-bit signed integer to represent the row IP or ROWID. This option is available by default unless explicitly specified using WITHOUT ROWID keywords.

The ROWID column gets automatically incremented as you add data to the table. For example, consider the query below to create and add data.

CREATE TABLE "demo" (
    "name"  TEXT,
    "role"  TEXT
);

Next, add data to the created table above using the query provided below:

INSERT INTO "main"."demo" ("name", "role") VALUES ('Mari Clovis', 'Web Developer');
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.

SELECT oid, name, role FROM demo;

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:

CREATE TABLE "demo2" (
    "Field1"    INTEGER,
    PRIMARY KEY("Field1" AUTOINCREMENT)
);

For example, to consider the table below:

CREATE TABLE "users" (
    "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 ('1', 'Danuše Bob', '23', 'Python, Java');
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:

 INSERT INTO "main"."users" ("No", "Name", "Age", "Language") VALUES ('1000', ' Chestirad Orval', '22', 'Java, Matlab, C#);

Now, if you query the data in the above table, you will see a table as shown below:

SELECT * FROM users;

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.

About the author

John Otieno

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list