CREATE INDEX

 CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name .] index-name 
  ON table-name ( column-name [, column-name]* ) 
  column-name = name [ COLLATE collation-name] [ ASC | DESC ] 

Description:

The CREATE INDEX command consists of the keywords CREATE INDEX followed by the name of the new index, the keyword ON, the name of an existing table that is to be indexed, and a parenthesized list of names of table columns that are used for the index key. Each column name can be followed by one of the ASC or DESC keywords to indicate sort order, but the sort order is ignored in the current implementation. Sorting is always done in ascending order.

The optional COLLATE clause following each column name defines a collating sequence used for text entries in that column. The default collating sequence is the one defined for that column in the CREATE TABLE statement. If no collating sequence is otherwise defined, the built-in BINARY sequence is used.

There are no arbitrary limits on the number of indexes that can be attached to a single table, nor on the number of columns in an index.

If the UNIQUE keyword appears between CREATE and INDEX, then duplicate index entries are not allowed. Any attempt to insert a duplicate entry will result in an error.

The exact text of each CREATE INDEX statement is stored in the sqlite_master or sqlite_temp_master table, depending on whether the table being indexed is temporary. Every time the database is opened, all CREATE INDEX statements are read from the sqlite_master table and used to regenerate QDB's internal representation of the index layout.

If the optional IF NOT EXISTS clause is present and another index with the same name aleady exists, then this command becomes a no-op.

Indexes are removed with the DROP INDEX command.