SQLite DROP INDEX Statement

Summary: in this tutorial, you will learn how to use the SQLite DROP INDEX statement to remove an index.

Introduction to the SQLite DROP INDEX statement

The DROP INDEX statement allows you to remove an index associated with a table. Here’s the syntax of the DROP INDEX statement:

DROP INDEX [IF EXISTS] index_name;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the index you want to remove after the DROP INDEX keywords.
• Second, use the optional IF EXISTS clause to conditionally delete the index only if it exists.

The DROP INDEX permanently removes the index_name from the SQLite database.

To get all indexes in the current attached database, you use the following statement:

SELECT
   name, 
   tbl_name, 
   sql
FROM
   sqlite_master
WHERE
   type= 'index';Code language: SQL (Structured Query Language) (sql)

The query returns the name of the index, the name of the table with which the index is associated, and the SQL statement that defines the index.

SQLite DROP INDEX statement examples

Let’s take some examples of using the DROP INDEX statement.

1) Removing indexes

First, create a new table called customers:

CREATE TABLE customers(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE    
);Code language: SQL (Structured Query Language) (sql)

Second, create an index on the name column:

CREATE INDEX customer_name 
ON customers(name);Code language: SQL (Structured Query Language) (sql)

Third, create a unique index on the email column:

CREATE UNIQUE INDEX customer_email
ON customers(email);Code language: SQL (Structured Query Language) (sql)

Fourth, retrieve all indexes of the current database:

SELECT
   name, 
   tbl_name, 
   sql
FROM
   sqlite_master
WHERE
   type= 'index';Code language: SQL (Structured Query Language) (sql)

Output:

name                          tbl_name   sql
----------------------------  ---------  ----------------------------------
sqlite_autoindex_customers_1  customers  null

customer_name                 customers  CREATE INDEX customer_name
                                         ON customers(name)

customer_email                customers  CREATE UNIQUE INDEX customer_email
                                         ON customers(email)Code language: SQL (Structured Query Language) (sql)

The output indicates that there are three indexes. One is created automatically when defining the table and two others are created using the CREATE INDEX statement.

Fifth, remove the customer_name index using the DROP INDEX statement:

DROP INDEX customer_name;Code language: SQL (Structured Query Language) (sql)

Verify the index removal:

SELECT
   name, 
   tbl_name, 
   sql
FROM
   sqlite_master
WHERE
   type= 'index';Code language: SQL (Structured Query Language) (sql)

Output:

name                          tbl_name   sql
----------------------------  ---------  ----------------------------------
sqlite_autoindex_customers_1  customers  null

customer_email                customers  CREATE UNIQUE INDEX customer_email
                                         ON customers(email)Code language: SQL (Structured Query Language) (sql)

The output indicates that the customer_name index has been removed successfully.

Finally, remove the customer_email index:

DROP INDEX customer_email;Code language: SQL (Structured Query Language) (sql)

2) Dropping an index that does not exist

The following statement uses the DROP INDEX statement to drop an index that does not exist:

DROP INDEX customer_phone;Code language: SQL (Structured Query Language) (sql)

It returns the following error:

Parse error: no such index: customer_phoneCode language: SQL (Structured Query Language) (sql)

To conditionally remove an index only if it exists, you can use the IF EXISTS option:

DROP INDEX IF EXISTS customer_phone;Code language: SQL (Structured Query Language) (sql)

This time, SQLite does not issue any errors.

Summary

• Use the DROP INDEX to remove an index from an SQLite database.
• Use the IF EXISTS option to conditionally drop an index if it exists.