Name
CREATE INDEX — Define and create a new table index
Syntax
Common Usage
CREATE INDEXindex_name
ONtable_name
(column_name
COLLATE NOCASE ); CREATE UNIQUE INDEXdatabase_name
.index_name
ONtable_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 ...
Get Using SQLite now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.