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.

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.