Db2 DROP INDEX

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

Introduction to Db2 DROP INDEX statement

The DROP INDEX deletes an index from a table. Here is the basic syntax of the DROP INDEX statement:

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

In this syntax, you specify the name of the index that you want to delete after the DROP INDEX keyword.

If you delete a non-existing index, Db2 will issue an error. Unfortunately, Db2 doesn’t support IF EXISTS option that conditionally deletes an index only if the index exists.

Notice that you cannot use the DROP INDEX statement to drop a primary key or unique key index.

If the primary index or unique key index was created automatically by the primary key constraint or unique constraint, to drop this kind of index, you use the ALTER TABLE statement to remove the primary key or unique constraint. Db2 will automatically remove the associated index.

If the primary index or the unique constraint was user-defined, then you must drop the primary key or unique key first by using the ALTER TABLE statement. After that, you can use the DROP INDEX statement to explicitly drop the index associated with the primary key or unique constraint.

Db2 DROP INDEX examples

Let’s take some examples of deleting an index

1) Using DROP INDEX statement to delete an index

This statement creates a new index on the title column of the books table:

CREATE INDEX ix_title
ON books(title);
Code language: SQL (Structured Query Language) (sql)

To drop the ix_title index, you use the following statement:

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

2) Using DROP INDEX statement to delete an index associated with the unique constraint

This example illustrates how to drop a unique index associated with a unique constraint.

First, create a new table named users that has a unique constraint on the email column:

CREATE TABLE users(
    user_id INT NOT NULL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

Second, create a unique index on the email column:

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

Third, add the unique constraint to the email column:

ALTER TABLE users 
ADD CONSTRAINT unq_email 
UNIQUE(email);
Code language: SQL (Structured Query Language) (sql)

Fourth, attempt to drop the ix_email index:

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

Db2 issued the following error:

SQL Error [42917]: A system required index cannot be dropped explicitly.. SQLCODE=-669, SQLSTATE=42917, DRIVER=4.21.29
Code language: SQL (Structured Query Language) (sql)

To delete the index, first, drop the unq_email constraint:

ALTER TABLE users
DROP CONSTRAINT unq_email;
Code language: SQL (Structured Query Language) (sql)

And then drop the ix_email index:

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

In this tutorial, you have learned how to use the Db2 DROP INDEX statement to delete an index.