SQLite Autoincrement

SQLite is a serverless database engine written in c programming language. It is one of the most used database engines in our everyday life like Mobile Phones, TV, and so on, etc.

In this article, we will be learning about autoincrement in SQLite, its functionality, and how it works along with the examples and we will also be covering without autoincrement too.

SQLite AUTOINCREMENT

AUTOINCREMENT keyword is used to increase the value of the field automatically. We need to use the autoincrement keyword to increase the field(column) value and it only works for the INTEGER fields. Autoincrement is used only with the columns that act as the primary keys because we use it only for the employee_id, and student_id i.e. for the id's and roll numbers that have sequential numbers as their values for the fields. This primary key acts as a foreign key for the other table.

Syntax:

CREATE TABLE table_name(
   column1 INTEGER AUTOINCREMENT,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

Example: SQLite without Autoincrement

SQLite without AUTOINCREMENT

By the heading itself, you can understand what keyword to use when there is no autoincrement keyword specified. For that, there is a default keyword for autoincrement i.e. ROWID which is not to be specified while creating the table. It is generated automatically and the column is not visible if you do not fetch the data by specifying that column. Now, let us learn about the Rowid.

Introduction to SQLite ROWID Table

Rowid is generated automatically without specifying the keyword. It also works the same as the autoincrement but the small difference is that you need to use the autoincrement keyword to increase the values of the column, whereas there is no need to use the rowid keyword to increase the value of the column as it is default keyword.

In simple words it is the implict auto-increment column i.e rowid.

let us use the products table that has three columns: prod_Id, prod_name and prod_price:

Create Products Table

let us create a products table by adding the fields like prod_Id, prod_name, prod_price and the datatypes are allocated according to their fields and create is the main keyword for creating the table.

CREATE TABLE products (
    prod_Id    INTEGER,
    prod_name    TEXT,
    prod_price    INTEGER
);

The products table created and now let us insert the data into it.

Inserting the Data into Products Table

The table created and now it’s time to insert the data into the table and we are inserting three recods and the text is written in single quotes as they are to be considered as text:

INSERT INTO products(prod_name, prod_price)
values('pens', 100),
('pencils', 50),
('books', 200);

And now 3 records are added into the products table and the data is inserted according to their fields. Let us fetch the data by suing the select statement as below.

Using Select Statement to Fetch the Data

select statement is used to fetch the data from the the table. Here we are going to retrieve all the data from the products table by using the syntax.

SELECT * from products;

As you can see that the prod_Id column has null values because we have not inserted the data.

Fetching the Rowid Column

Here you need to remember one point that rowid will not be visible until and unless you select the rowid column to fetch. Along with rowid we are also fetching the Id column too because you can get the clear knowledge on it.

you can see that the rowid is generated automatically in the below output that means we have not generated the field i.e rowid in our faculty table as you can see above in the create syntax.

Example: SQLite AUTOINCREMENT

For understanding of AUTOINCREMENT concepts in more deeply, we need a table on which we will perform the operations. Let's create a table called faculty which consist Id, name, salary, dept as Columns. Since AUTOINCREMENT keyword works only on the columns which INTEGER values in it. Here all the columns are of INTEGER data types and we make Id column as a PRIMARY KEY.

Let us use the customers table to know more about the SQLite Autoincrement. For this first let us create customers table by using below syntax.

Creating the Customers Table

CREATE TABLE "customers" (
    "cust_Id"    INTEGER,
    "cust_name"    TEXT,
    "cust_address"    TEXT,
    PRIMARY KEY("cust_Id" AUTOINCREMENT)
);

The customers table consists of three columns, they are cust_Id, cust_name and cust_address and the datatypes are given according to their fields and the customers table is created.

Insert the Data into the Customers Table

Let us try to insert the data into the customers table by using the insert statement as shown below. You can see that we have not inserted the data into the cust_Id field as it is autoincrement field.

INSERT INTO customers(cust_name, cust_address)
values ( 'Jim', 'California'),
('Rose', 'New York'),
('Mark', 'Texas'),
('David', 'Houston');

Now we have inserted the four records into the customers table and now let us try to fetch the data using the select statement. Now let us see whether the cust_Id field is autoincremented or not?

Fetching the Data Using Select Statement

Now let us use the Select statement to fetch the data.

As you can see that customers table is fetched with the three columns and they are cust_Id, cust_name and cust_address along with the data inserted inside the table and you can also observe that cust_Id column is autoincremented.

Conclusion

So, the main usage of autoincrement is not to use the repeated values and to use unique values for the rows. Autoincrement key is used with the primary key only that means for the columns that are to be incemented in the sequential order. Coming to the Rowid it is the Default keyword, that need not be specified while creating the table but, if you want to see it in the output you need to use thr Rowid keyword in the select table to fetch it.