SQLite Create and Drop Index

Introduction

An index is a special data structure , which stores the values for an entire column (or columns) in a highly organized manner that is optimized for searching.

Indexes (or indices) are very special to a database lookups, because, this allows specific rows in a table to be found without having to scan every row in the table. Hence, indexes can provide a large performance boost to some types of queries. Indexes are always associated with only one table, but more columns of the table can be included in the indexes.

Usually, the indexes can allow duplicate values, but with the UNIQUE keyword restricts the duplicity for any attempt to insert or update a table with a nonunique value. As NULL isn’t considered a value, so a UNIQUE index will not prevent one or more NULLs. To prevent NULLs, you must indicate NOT NULL in the original table definition.

Dropping an index will remove the index from the database, but the associated table will remain intact.

Syntax:

CREATE [UNIQUE] INDEX index_name ON table_name ( column_name [, ...] );

Parameters:

index_name - Name of the index.

table_name - Name of the table with which the index is associated.

column_name - Name of the column(s)

Create Index:

Creating an Index on a Column

Here we are creating a table company.

CREATE TABLE company(
com_id text(4),
com_name text(15));

Here, we added an index named com_id_index on the column "com_id" of company table. Here is the statement.

CREATE INDEX com_id_index ON company (com_id);

Create Index on Multiple Columns

Now, we want to add an index named com_id_name on the columns "com_id" and "com_name":

CREATE INDEX com_id_name ON company (com_id,com_name);

This type of index is called composit index, because two or more columns collectively make this type index.

Create UNIQUE Index

If you want to add an index named unique_com_id on com_id column of company table, the following statement can be used.

CREATE UNIQUE INDEX unique_com_id ON company (com_id,com_name);

After creating a UNIQUE index if you are trying to insert a record into the table that already exists in the UNIQUE Index, this leads to an error message. In a UNIQUE index, there cannot be any duplicate entries.

Here is some examples:

sqlite> INSERT INTO company VALUES ('com_001', 'company-1');
sqlite> INSERT INTO company VALUES ('com_002', 'company-2');
sqlite> INSERT INTO company VALUES ('com_001', 'company-1');
Error: UNIQUE constraint failed: company.com_id, company.com_name	

Here in the above you can see that uniqueness is defined by both columns collectively, not individually and uniqueness is violeted here.

Here is the list of indexes created on company table:

sqlite> .indices company
com_id_index
com_id_name
unique_com_id

If you want to see database wise all indexes, use the following statement.

sqlite> SELECT * FROM sqlite_master WHERE type = 'index';
type                    name                tbl_name    rootpage    sql
----------------------  ------------------  ----------  ----------  ---------------------------------------------
index                   com_id_index        company     3           CREATE INDEX com_id_index ON company (com_id)
index                   com_id_name         company     4           CREATE INDEX com_id_name ON company (com_id,c
index                   unique_com_id       company     5           CREATE UNIQUE INDEX unique_com_id ON company(

Collation

The COLLATE keyword is used to make a column case-insensitive associated with index. This means that values in the indexed column will sort without respect to the case.

If you want to create a caseinsensitive index on com_name, the following index statement can be used.

CREATE INDEX com_name_collate on company (com_name COLLATE NOCASE);

To get the details information for the table company, use the following command:

sqlite> .schema company
CREATE TABLE company(
com_id text(4),
com_name text(15));
CREATE INDEX com_id_index ON company (com_id);
CREATE INDEX com_id_name ON company (com_id,com_name);
CREATE UNIQUE INDEX unique_com_id ON company (com_id,com_name);
CREATE INDEX com_name_collate on company (com_name COLLATE NOCASE);

Drop Index

The DROP INDEX command will drop the index from the databse but will leave the associated table intact.

Syntax:

DROP INDEX index_name;

Example:

If you want to drop the index com_name_collate from the database, the following can be used.

DROP INDEX com_name_collate;

 
Previous:
 Constraint

Next: 
 SELECT statement