Indexes

The subject of keys and indexes and how they can affect the performance of your database will be addressed in Chapter 4, "Query Optimization," but first we will examine the syntax for creating and finding information on table indexes.

Creating and Dropping Indexes

The CREATE INDEX command is used to add a new index to a database table, using this syntax:

 CREATE [UNIQUE] INDEX index-name ON [database-name .] table-name (column-name [, column-name]*) [ON CONFLICT conflict-algorithm] 

The index-name is a user-provided identifier for the new index and must be unique across all database objects. It cannot take the same name as a table, view, or trigger. A popular naming convention is to use the table name and the column name(s) used for the index key separated by an underscore character.

To add an index to the color column of the vegetables table, we would use the following command.

 sqlite> CREATE INDEX vegetables_color    ...> ON vegetables(color); 

The syntax of column-name allows for a sort order to be given after each column name, either ASC or DESC; however, currently in SQLite this is ignored. At the present time, all indexes are created in ascending order.

Removing an index is done with reference to the identifier given when it was created, which you can always find by querying the sqlite_master table if you cannot remember it.

 sqlite> SELECT * FROM sqlite_master    ...> WHERE type = 'index';     type = index     name = vegetables_color tbl_name = vegetables rootpage = 10      sql = CREATE INDEX vegetables_color ON vegetables(color) 

The DROP INDEX command works as you might expect:

 sqlite> DROP INDEX vegetables_color; 

Don't worry if you misread the sqlite_master output and use the table name instead of the index name. SQLite only allows you to drop indexes with the DROP INDEX command and tables with the DROP TABLE command.

 sqlite> DROP INDEX vegetables; SQL error: no such index: vegetables 

UNIQUE Indexes

The UNIQUE keyword is used to specify that every value in an indexed column is unique. Where an index is created on more than one column, every permutation of the column values has to be unique, even though the same value may appear more than once in its own column.

Since we have already inserted several vegetables of the same color into the table, SQLite will give an error if we attempt to create a unique index on the color field.

 sqlite> CREATE UNIQUE INDEX vegetables_color    ...> ON vegetables(color); SQL error: indexed columns are not unique 

The ON CONFLICT clause at the index level is only relevant for a UNIQUE index; otherwise, there will never be a conflict on the data it applies to. The conflict resolution algorithm is used when an INSERT, UPDATE, or COPY statement would cause the unique constraint of the index to be violated. It cannot be used in the preceding CREATE UNIQUE INDEX statement to force a unique index onto a column containing multiple values.

The default conflict resolution algorithm is ABORT, and the same list of algorithms is permitted for indexes as in the CREATE TABLE statement.