SQLite - Index (indices)

About

Relation - Index (Indices) in SQLite - SQLite command line

Internally, SQLite assumes that indices stored in database files are sorted according to the collation sequence indicated by the SQL schema. Changing the definition of a collation sequence after an index has been built is therefore equivalent to database corruption.

Management

Create

• Unique ¹⁾

CREATE UNIQUE INDEX IndexName ON TableName (colName1, ...);

Indexes can use:

• order (ie ASC/DESC)
• and COLLATE

Drop

²⁾

'drop' 'index' ['if' 'exists'] [ 'schema-name' '.'] 'index-name'

Alter

There is no alter index statement support. You need to drop and create again.

List

via pragma_index_info

SELECT DISTINCT m.name || '.' || ii.name AS 'indexed-columns'
  FROM sqlite_master AS m,
       pragma_index_list(m.name) AS il,
       pragma_index_info(il.name) AS ii
 WHERE m.type='table'
 ORDER BY 1;

Reference