Learning + translation of SQL as understood by SQLite -- create Index

SQL as understood by SQLite

[Contents]

Create Index

SQL-Statement: =	Create[Unique]Index[Database-name .] Index-name OnTable-name ( Column-name [, Column-name]* ) [On conflictConflict-Algorithm ]
Column-name: =	Name [CollateCollation-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 previusly 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, but the sort order is ignored in the current implementation. sorting is always done in ascending order.

"Create Index" is followed by the name of the new index. the index is generated for the table connected with the keyword "on. an index is a new column named "(" + column name in the table +. you can use the keyword "ASC" or "DESC" to specify the order of the column. (The two keywords are ignored in the current version in ascending order ).

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.

(What does this paragraph mean ?) The collate statement follows the names of each column and defines the order of input content in the column. the default order is defined during create table. if not defined separately, the binary compilation sequence is applied.

There are no arbitrary limits on the number of indices 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.

If the keyword unique is used between create and index, duplicate indexes are not allowed. Attempts to insert duplicate entries will cause errors.

The optional conflict-clause allows the specification of an alternative default constraint Conflict Resolution Algorithm for this index. this only makes sense if the unique keyword is used since otherwise there are not constraints on the index. the default algorithm is abort. if a copy, insert, or update statement specifies a participant Conflict Resolution Algorithm, that algorithm is used in place of the default algorithm specified here. see the section titled on conflict for additional information.

The exact text of each create index statement is stored inSqlite_masterOrSqlite_temp_masterTable, depending on whether the table being indexed is temporary. Every time the database is opened, all create index statements are read fromSqlite_masterTable and used to regenerate SQLite's internal representation of the index layout.

The exact create index status is saved inSqlite_masterOrSqlite_temp_master(This depends on whether the table is temporarily indexed). When the database is opened,Sqlite_masterThe status of all indexes is read to generate the index layout in SQLite.

Indexes are removed with the drop Index Command.

This page last modified on 16:11:41