Overview
The SQLite CREATE INDEX statement is used to create an index on one or more columns of a database table. Indexes can significantly improve the performance of SELECT queries by allowing the SQLite database engine to locate and retrieve data more efficiently.
SQLite CREATE INDEX Statement
Syntax
The general syntax of the CREATE INDEX statement is as follows:
CREATE INDEX index_name
ON table_name ( column_name);
or
CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );
The COLLATE NOCASE clause is used to create case-insensitive indexes. By default, SQLite performs case-sensitive comparisons. However, comparisons become case-insensitive when you use the COLLATE NOCASE clause in the index statement.
or
On multiple columns:
CREATE UNIQUE INDEX index_name
ON table_name ( colum1, column2, …… columnN) ;
The UNIQUE keyword indicates that the values in the indexed columns must be unique. This enforces a uniqueness constraint on the indexed columns.
Example
In this example, let’s create an index named indx_empno on the empno column of the emp table.
CREATE INDEX indx_empno
ON emp (empno);
We can verify the index using the .schema command.
sqlite> .schema emp
CREATE TABLE emp (
empno INTEGER PRIMARY KEY,
ename TEXT NOT NULL,
jobtitle TEXT NOT NULL,
salary INTEGER NOT NULL, bonus INTEGER DEFAULT 0);
CREATE INDEX indx_empno
ON emp (empno COLLATE NOCASE);
sqlite>
—
SQLite Tutorials
SQLite tutorials on this website:
https://www.testingdocs.com/sqlite-database-tutorials/
For more information on SQLite, visit the official website: