{"id":796,"date":"2019-06-09T02:46:03","date_gmt":"2019-06-09T10:46:03","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=796"},"modified":"2020-04-11T06:58:11","modified_gmt":"2020-04-11T14:58:11","slug":"db2-drop-index","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-index\/db2-drop-index\/","title":{"rendered":"Db2 DROP INDEX"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 DROP INDEX<\/code> statement to delete an index.<\/p>\n\n\n\n

Introduction to Db2 DROP INDEX statement<\/h2>\n\n\n\n

The DROP INDEX<\/code> deletes an index from a table. Here is the basic syntax of the DROP INDEX<\/code> statement:<\/p>\n\n\n

DROP<\/span> INDEX<\/span> index_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the name of the index that you want to delete after the DROP INDEX<\/code> keyword.<\/p>\n\n\n\n
If you delete a non-existing index, Db2 will issue an error. Unfortunately, Db2 doesn’t support IF EXISTS<\/code> option that conditionally deletes an index only if the index exists.<\/p>\n\n\n\n
Notice that you cannot use the DROP INDEX<\/code> statement to drop a primary key or unique key index.<\/p>\n\n\n\n
If the primary index or unique key index was created automatically by the primary key constraint<\/a> or unique constraint<\/a>, to drop this kind of index, you use the ALTER TABLE<\/code> statement to remove the primary key or unique constraint. Db2 will automatically remove the associated index.<\/p>\n\n\n\n
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<\/code> statement. After that, you can use the DROP INDEX<\/code> statement to explicitly drop the index associated with the primary key or unique constraint.<\/p>\n\n\n\n
Db2 DROP INDEX examples<\/h2>\n\n\n\n
Let’s take some examples of deleting an index<\/p>\n\n\n\n
1) Using DROP INDEX statement to delete an index<\/h3>\n\n\n\n
This statement creates a new index<\/a> on the title<\/code> column of the books<\/code> table:<\/p>\n\n\n
CREATE<\/span> INDEX<\/span> ix_title\nON<\/span> books(title);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To drop the ix_title<\/code> index, you use the following statement:<\/p>\n\n\n
DROP<\/span> INDEX<\/span> ix_title;    \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using DROP INDEX statement to delete an index associated with the unique constraint<\/h3>\n\n\n\n
This example illustrates how to drop a unique index associated with a unique constraint<\/a>.<\/p>\n\n\n\n
First, create a new table<\/a> named users<\/code> that has a unique constraint on the email<\/code> column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> users<\/span>(\n    user_id INT<\/span> NOT<\/span> NULL<\/span> PRIMARY KEY<\/span>,\n    email VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    password<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create a unique index on the email<\/code> column:<\/p>\n\n\n
CREATE<\/span> UNIQUE<\/span> INDEX<\/span> ix_email \nON<\/span> users<\/span>(email);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, add the unique constraint to the email<\/code> column:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> users<\/span> \nADD<\/span> CONSTRAINT<\/span> unq_email \nUNIQUE<\/span>(email);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"db2<\/figure>\n\n\n\n
Fourth, attempt to drop the ix_email<\/code> index:<\/p>\n\n\n
DROP<\/span> INDEX<\/span> ix_email;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Db2 issued the following error:<\/p>\n\n\n
SQL Error [42917]: A system required index cannot be dropped explicitly.. SQLCODE=-669, SQLSTATE=42917, DRIVER=4.21.29\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To delete the index, first, drop the unq_email<\/code> constraint:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> users<\/span>\nDROP<\/span> CONSTRAINT<\/span> unq_email;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
And then drop the ix_email<\/code> index:<\/p>\n\n\n
DROP<\/span> INDEX<\/span> ix_email;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to use the Db2 DROP INDEX<\/code> statement to delete an index.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t