Using SQLite

Name

CREATE INDEX — Define and create a new table index

Syntax

Common Usage

CREATE INDEX index_name ON table_name ( column_name COLLATE NOCASE );
CREATE UNIQUE INDEX database_name.index_name ON table_name ( col1, col2 ,... );

Description

The CREATE INDEX command creates a user-defined index. Upon creation, the index is populated from the existing table data. Once created, the index will be automatically maintained, so that modifications to the referenced table will be reflected in the index. The query optimizer will automatically consider any indexes that have been created. Indexes cannot be created on virtual tables or views.

An index can reference multiple columns, but all of the columns must be from the same table. In the case of multicolumn indexes, the index will be built in the same order as the column listing. For performance-related indexes, the column ordering can be very important. See Order Matters for more details. The table must be in the same database as the index. To create an index on a temporary table, create the index in the temp database.

If a table is dropped, all associated indexes are also dropped. A user-defined index may also be explicitly dropped with the DROP INDEX command.

If the optional UNIQUE clause is included, the index will not allow inclusion of equivalent index entries. An index entry includes the whole set of indexed columns, taken as a group, so you may ...