Small. Fast. Reliable.
Choose any three.

About Sitemap Documentation Download License News Developers Support

## SQL As Understood By SQLite

### CREATE INDEX

sql-statement ::= | 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 ]

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 names of columns in the table 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. The sort order may or may not be ignored depending on the database file format. The "legacy" file format ignores index sort order. The descending index file format takes index sort order into account. Only copies of SQLite newer than version 3.3.0 (released on 2006-01-10) are able to understand the newer descending index file format and so for compatibility with older versions of SQLite, the legacy file format is generated by default. Use the legacy_file_format pragma to modify this behavior and generate databases that use the newer file format. Future versions of SQLite may begin to generate the newer file format by default.

The COLLATE clause following each column name defines a collating sequence used for text entires 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.

There are no arbitrary limits on the number of indices that can be attached to a single table. The number of columns in an index is limited to SQLITE_MAX_COLUMN.

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. For the purposes of unique indices, all NULL values are considered to different from all other NULL values and are thus unique.

The 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 SQLite'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.

This page last modified 2008/04/27 17:48:52 UTC