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.

customers table

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.