An index is a specific data tool for organizing and optimizing the information for a whole column for querying. Indexes (or indices) are unique to database search in that they allow certain rows in a record to be located without needing to scan the whole table. As a result, indices can give some operations a significant performance gain. Indexes are usually connected with a single table, although they might comprise several columns from that table.
Thus, we have decided to cover the topic of indexes used within SQLite in this article today. Let’s get started with the SQLite database on our just launched terminal shell. For this, try out the one-word command in the query area, i.e., “sqlite3”. The SQLite shell console will be launched, as shown below:
After launching the SQLite shell, we have used the “.tables” instruction on its shell to display all the already made tables of our database.
Thus, we need at least one table within our database to create indexes on its columns. So, we are trying the CREATE TABLE instruction that will create a table named “Info”. This table will contain a total of three columns within it. The ID column and Age column will be of Integer type, while the ID column will be used as a Primary key of this table. The Name column will be of text type. The table has been successfully created, and we have tried the SELECT instruction to fetch all its records. Unfortunately, there are no records in the table so far.
sqlite> SELECT * FROM Info;
sqlite>.tables
Let’s start inserting several records in the table Info using the INSERT INTO instruction of the SQLite database. For this, you need to specify the names of table columns used to insert records, i.e., ID, Name, and Age. The column names will be followed by the keyword “VALUES” using the values within the brackets. We have added five records within the table “Info”.
sqlite> INSERT INTO Info(ID, Name, Age) VALUES (2, "Bella", 23);
sqlite> INSERT INTO Info(ID, Name, Age) VALUES (3, "George", 26);
sqlite> INSERT INTO Info(ID, Name, Age) VALUES (4, "George", 29);
sqlite> INSERT INTO Info(ID, Name, Age) VALUES (5, "Ema", 30);
After that, we used the SELECT instruction to display all the newly inserted records of the table Info. The five records for columns ID, Name, and Age of a table have been displayed on the shell.
As we all know, the ID column of the “Info” table is already specified as a Primary key. Thus, we will check how a primary key constraint will react to the duplicate value insertion. So, we have been using the INSERT INTO instruction to insert new records within the table “Info”, while the value for column “ID” is 5, i.e., repeated. The execution of this statement leads to the error “UNIQUE constraint failed: Info.ID”. This means we cannot add a duplicate value for the column “ID”.
Single Index
Let’s create a Single Index on a particular column of an Info table. For this, you need to utilize the CREATE INDEX instruction followed by the name of an index to be created, i.e., age_index, the keyword “ON”, the name of a table, i.e., “Info” followed by the column name, i.e., “Age” on which we will be applying the index. After creating an index “age_index” for the column “Age”, we have inserted the new record within the table Info using the INSERT INTO instruction. We have been repeating the value for column “Age” in this instruction, i.e., 30. Upon searching all the records of a table “Info”, we have the following records:
sqlite> INSERT INTO Info(ID, Name, Age) VALUES (6, "Ema", 30);
sqlite> SELECT * FROM Info;
The same thing has been done for the column “Name”, i.e., created an index “name_index”. Next, we inserted the duplicate record for the column “Name” using the INSERT INTO instruction and displayed all the records of the table “Info”.
sqlite> INSERT INTO Info(ID, Name, Age) VALUES (7, "Ema", 20);
sqlite> SELECT * FROM Info;
You can list all the newly created indexes of your database using the “.indices” instruction along with the particular name of a table, “Info”, that contains or uses those indexes.
Composite Index
If you create an index for more than one column of a specific table, it is said to be the Composite Index. So, we have been creating a composite index “comp_index” for columns Name and Age of a table “Info” using the CREATE INDEX instruction displayed in the image. When we tried to insert duplicate records within the Name and Age column of table “Info” with the INSERT INTO instruction, it returned the error “UNIQUE Constraint failed”.
sqlite> INSERT INTO Info(ID, Name, Age) VALUES (7, "Bella", 23);
Typically, indexes offer the possibility of creating duplicate entries. However, the UNIQUE keyword prevents repeated values from being inserted or updated into a record with a non-unique entry. A UNIQUE index might not prohibit one or additional NULLs since NULL isn’t regarded as a value. NOT NULL should be specified in the initial table declaration to avoid NULLs. Let’s create a UNIQUE index on column “ID” of a table Info with the CREATE INDEX instruction. We have seven records for the table “Info” of this database. So, we tried the INSERT INTO instruction to add the duplicate value for the ID column of a table. It returned the same “UNIQUE constraint failed” error on our screen.
sqlite> SELECT * FROM Info;
sqlite> INSERT INTO Info(ID, Name, Age) VALUES (7, "Ana", 19);
Let’s create a new table, “Test”, with two columns ID and AGE, with no primary keys. After that, we created a unique index, “unique_index”, on both of its columns and added two unique records and one duplicate record with the INSERT INTO instruction. The third record throws an error because both columns’ values have been repeated.
sqlite> CREATE UNIQUE INDEX unique_index ON TEST (ID, Age);
sqlite> INSERT INTO TEST(ID, Age) VALUES (1, 23);
sqlite> INSERT INTO TEST(ID, Age) VALUES (2, 24);
sqlite> INSERT INTO TEST(ID, Age) VALUES (2, 24);
Conclusion:
This article was about the usage of different indexes of the table columns. We discussed the uses of indexes and tried to create the single, composite, and unique indexes on different table columns with the CREATE INDEX instruction at our end.
