Small. Fast. Reliable.
Choose any three.
Search for:

SQL As Understood By SQLite




syntax diagram create-index-stmt



The CREATE INDEX command consists of the keywords "CREATE INDEX" followed by the name of the new index, the keyword "ON", the name of a previously created table that is to be indexed, and a parenthesized list of table column names and/or expressions that are used for the index key. If the optional WHERE clause is included, then the index is a "partial index".

Each column name or expression can be followed by one of the "ASC" or "DESC" keywords to indicate sort order. The sort order may or may not be ignored depending on the database file format, and in particular the schema format number. The "legacy" schema format (1) ignores index sort order. The descending index schema format (4) takes index sort order into account. Only versions of SQLite 3.3.0 (2006-01-11) and later are able to understand the descending index format. For compatibility, version of SQLite between 3.3.0 and 3.7.9 use the legacy schema format by default. The newer schema format is used by default in version 3.7.10 (2012-01-16) and later. The legacy_file_format pragma can be used to change set the specific behavior for any version of SQLite.

The COLLATE clause optionally following each column name or expression defines a collating sequence used for text entries in that column. The default collating sequence is the collating sequence defined for that column in the CREATE TABLE statement. Or if no collating sequence is otherwise defined, the built-in BINARY collating sequence is used.

Expressions in an index may not reference other tables and may not use subqueries nor functions whose result might change (ex: random() or sqlite_version()). Expressions in an index may only refer to columns in the table that is being indexed. Indexes on expression will not work with versions of SQLite prior to version 3.9.0 (2015-10-14). See the Indexes On Expressions document for additional information about using general expressions in CREATE INDEX statements.




Indexes are removed with the DROP INDEX command.