SQLite: Creating an Index on one or more Columns

In this info, I want to show you how you can add indices over one or more columns of a table in a SQLite database.

As far as I know, in the CREATE TABLE statement, you can only create a PRIMARY KEY index. All other indices must be created in further requests after the creation of the table.

Creating an Index in the CREATE TABLE Statement

First, let us have a look at a typical CREATE TABLE statement for creating a new table.

CREATE TABLE IF NOT EXISTS tab (
id INTEGER PRIMARY KEY, 
firstname TEXT, 
lastname TEXT)

We are creating a table named "tab", if such a table is not existing yet. The table consists of the columns id, firstname and lastname. Behind the definition of "id INTEGER", we have written "PRIMARY KEY". This ensures that our column "id" is the primary index of our table.

Creating an Index on a Column

Next, we would like to add an index on the column "lastname". For this, we have to use the following syntax:

CREATE INDEX index_name ON tab_name (column) 

Or in our example:

CREATE INDEX index_lastname ON tab (lastname)

We have named our index "index_lastname" and otherwise, we have simply inserted the names of the table and the column.

Create Index on Multiple Columns

If we want to create an index on multiple columns, we can use the following syntax:

CREATE INDEX index_name ON tab_name (column1, column2) 

In our example, we want to add an index on the columns "firstname" and "lastname":

CREATE INDEX index_firstandlastname ON tab (firstname, lastname)

Multiple column names are just separated by a comma, otherwise the structure of the statement remains the same.

Additional Specifications

If we only wants to create the index if it is not yet existing, we can add "IF NOT EXISTS" to our statement:

CREATE INDEX IF NOT EXISTS index_name ON tab_name (spalte) 

If the index should be UNIQUE (not more than one same value in the index), we complement UNIQUE:

CREATE UNIQUE INDEX index_name ON tab_name (spalte) 

If you have created a UNIQUE index and if you are trying to insert a record into the table that already exists in the UNIQUE Index, this leads to an error message. In a UNIQUE index there cannot be any duplicate entries.