MariaDB Drop Index

In this MariaDB tutorial, we will look at the MariaDB Drop Index statement and several examples.

MariaDB Drop Index

In this section, we will learn how to drop the index using an SQL query in MariaDB. We will explain this with an illustrated example.

In MariaDB, the DROP INDEX statement removes the index from the table. Here is the syntax of the DROP INDEX statement by the following query:

SYNTAX:

DROP INDEX [IF EXISTS] YOUR_INDEX_NAME
ON YOUR_TABLE_NAME
[alogrithim_option | lock_option];

Here is the syntax explanation:

• First, we need to specify the name of the index that we want to delete after the DROP INDEX keyword. The IF EXISTS clause is optional and the condition removes the index if it exists.
• Second, we need to specify the name of the table to which it associates after the ON keyword.
• Third, specify the algorithm or lock option that is used to remove the index operation.

First, we will create and show an index from the STATES_OF_USA table by the following query:

CREATE INDEX STATEID 
ON STATES_OF_USA (STATE_ID);

SHOW INDEXES FROM STATES_OF_USA;

In the first query, we have created a new index called STATEID for the STATE_ID column from the STATES_OF_USA table by using the CREATE INDEX statement. If the user wants to see all the indexes from the STATES_OF_USA table, then we will use the SHOW INDEXES statement.

Here is the example to drop the index by the following query:

EXAMPLE:

DROP INDEX STATEID ON STATES_OF_USA;

SHOW INDEXES FROM STATES_OF_USA;

The MariaDB DROP INDEX statement is used to remove the STATEID index from the STATES_OF_USA table. If we want to verify the removal of the STATEID index from the STATES_OF_USA table, then use the SHOW INDEXES statement.

Read: MariaDB Delete From Statement

MariaDB Drop Index Primary Key

Here, we will understand how to drop the index of the PRIMARY KEY column in MariaDB and we will also cover an example.

If the primary key contains the auto_increment column in the MariaDB table, then we need to remove the auto_increment property from the primary key column before we remove it from the primary key index. The following example illustrates how to drop the primary key index from the STATES_OF_USA table as given below:

EXAMPLE:

ALTER TABLE STATES_OF_USA STATE_ID INT;

In the query, we will remove the AUTO_INCREMENT property from the STATE_ID column of the STATES_OF_USA table by using the ALTER TABLE statement.

DROP INDEX PRIMARY ON STATES_OF_USA;

SHOW INDEXES FROM STATES_OF_USA;

Second, we will drop the primary index. Please note that the primary key is surrounded by the backside single braces character(`). Then, we will finally use the SHOW INDEXES statement to show all indexes from the STATES_OF_USA table.

Read: How to Drop Column from MariaDB Table

MariaDB Drop Index If Exists

In this section, we will learn how to use the IF EXISTS clause in MariaDB to drop an index. We will explain this using an example.

In MariaDB, the IF EXISTS clause is a condition that removes the index only if it exists in the table. Here is an example of the IF EXISTS clause to drop the index by the following query:

CREATE INDEX STATESNAME ON STATES_OF_USA(STATE_NAME);

DROP INDEX IF EXISTS STATENAME ON STATES_OF_USA;

Using the CREATE INDEX statement, we generated a new index called STATESNAME on the STATE_NAME column from the STATES_OF_USA database in the first query. Then, using the IF EXISTS clause as a condition, it will determine whether the STATENAME index exists in the STATES_OF_USA table or not, which will be accomplished using the DROP INDEX statement.

But it will drop a warning sign by using the IF EXISTS clause that the STATENAME index does not exist in the STATES_OF_USA table.

Here is another example of using the IF EXISTS clause to drop the correct index by using the following query:

DROP INDEX IF EXISTS STATESNAME ON STATES_OF_USA;

In the preceding query, the IF EXISTS clause condition will drop a STATESNAME index from the STATES_OF_USA table using the DROP INDEX statement. As the condition is meant to be true, the query is executed, and we get the result set.

Read: MariaDB Rename Table

MariaDB Alter Table Drop Index If Exists

Here, we will understand how to use the ALTER TABLE statement in MariaDB to drop the index by using the IF EXISTS clause in the query. This is explained using syntax and an illustrated example.

The MariaDB ALTER TABLE statement is used to modify, add, and delete columns from the table and rename columns. The MariaDB index is an achievement method for retrieving records faster. For each value in the indexed columns, an index generates an entry.

The syntax of using the ALTER TABLE statement with the IF EXISTS clause is given below:

SYNTAX:

ALTER TABLE YOUR_TABLE_NAME DROP INDEX IF EIXSTS YOUR_INDEX_NAME; 

Here is an example of how to use the ALTER TABLE statement with the IF EXISTS clause in the following query:

EXAMPLE:

CREATE INDEX STATESPOPULATION ON STATES_OF_USA(STATE_POPULATION);

ALTER TABLE STATES_OF_USA DROP INDEX IF EXISTS STATESPOPULATION;

• In the first query, we created a new index called STATESPOPULATION on the STATE_POPULATION column from the STATES_OF_USA table by using the CREATE INDEX statement.
• After that, we dropped the STATESPOPULATION index from the STATES_OF_USA table by using the IF EXISTS condition to check if the condition is proper.
• Then, it will drop the index name STATESPOPULATION from the STATES_OF_USA table using the ALTER TABLE statement.
• Otherwise, it will produce a warning saying that the IF EXISTS clause doesn’t drop an index called STATESPOPULATION from the STATES_OF_USA table.

Read: MariaDB GROUP BY with Example

MariaDB Drop Index By Name

In this section, we will use an example to drop an index from the FIRST_NAME column in the query. First, let’s have a look at the CUSTOMER_LIST table by the following query:

SELECT * FROM CUSTOMER_LIST;

The MariaDB SELECT statement retrieves all records from the CUSTOMER_LIST table.

Now, we’ll create an index on the FIRST_NAME column from the CUSTOMER_LIST table in the query as given below:

CREATE INDEX USA_PERSONNAME
ON CUSTOMER_LIST (FIRST_NAME);

SHOW INDEXES FROM CUSTOMER_LIST;

Here in the first query, we have created a new index called USA_PERSONNAME on the FIRST_NAME column from the CUSTOMER_LIST table by using the CREATE INDEX statement. If we want to see the index created properly on the CUSTOMER_LIST table then we will use the SHOW INDEXES statement.

Now, drop the index that we have created on the FIRST_NAME column by the following query:

EXAMPLE:

DROP INDEX USA_PERSONNAME ON CUSTOMER_LIST;

SHOW INDEXES FROM CUSTOMER_LIST;

In the first query, we have to use the DROP INDEX statement to remove the USA_PERSONNAME index from the CUSTOMER_LIST table.

In the second query, if we want to verify that the USA_PERSONNAME index has been properly removed from the CUSTOMER_LIST table, we can use the SHOW INDEXES statement.

Read: MariaDB Select Into + Examples

MariaDB Cannot Drop Index Needed in a Foreign Key Constraint

We need to understand why the MariaDB Database can’t drop index in a Foreign Key Constraint, and for that, we need to understand it with the help of an illustrated example.

In MariaDB, the foreign key is a kind of primary key to another table that contains no null value or duplicate value. The foreign key constraint column is in the child table, whereas the foreign key, which is referred to as the Primary Key column, is in the parent table.

Let’s make an index of the Foreign Key constraint from the SKULLCANDY table by the following query:

CREATE INDEX SKULLCANDYID
ON SKULLCANDY (SKULLCANDY_ID);

SHOW INDEXES FROM SKULLCANDY;

• As we see in the first query, we created the index SKULLCANDYID on the SKULLCANDY_ID column from the SKULLCANDY table using the CREATE INDEX statement.
• If we use the SHOW INDEXES statement, it will show the index that we created, SKULLCANDYID, from the SKULLCANDY_ID column of the SKULLCANDY table.

Here is an example to drop the index of the foreign key constraint by the following query:

EXAMPLE:

SET FOREIGN_KEY_CHECKS =0 // DISABLED

ALTER TABLE SKULLCANDY DROP INDEX SKULLCANDYID;

SET FOREIGN_KEY_CHECKS =1 // ENABLED

SHOW INDEXES FROM SKULLCANDY;

• In the first query, it is already understandable that we have disabled the foreign_key_checks as 0.
• Then, using the ALTER TABLE statement, we dropped the SKULLCANDYID index from the SKULLCANDY table.
• After that, we set the foreign_key_checks again to enabled so that the index of the foreign key constraint will still be tied to the parent table.
• In the last query, we used the SHOW INDEXES statement on the SKULLCANDY table to get relief from the INDEX removed on the SKULLCANDY_ID column.

Read: MariaDB Update Statement

MariaDB Drop Unique Index

Here, we will understand how to remove the unique index key from the MariaDB table and explain it using an illustrated example.

The UNIQUE INDEX modification in MariaDB specifies that the variables in the indexed columns must be unique. Let’s first create the UNIQUE INDEX key in the query by using the CREATE INDEX statement as shown below:

CREATE UNIQUE INDEX SKULLCANDY_NAME
ON SKULLCANDY (NAME);

SHOW INDEXES FROM SKULLCANDY;

Using the CREATE INDEX statement, we built a unique index called SKULLCANDY_NAME on the NAME column of the SKULLCANDY database in the first query. The SHOW INDEXES statement can be used to determine whether the INDEX column for the NAME column has been generated or not.

Now, here’s is the illustrated example of dropping a unique index from the table by using the DROP INDEX statement as shown below:

EXAMPLE:

DROP INDEX SKULLCANDY_NAME FROM SKULLCANDY;

SHOW INDEXES FROM SKULLCANDY;

In the first query example, we used the DROP INDEX statement to remove the index SKULLCANDY_NAME from the SKULLCANDY table. In the second query, we must determine whether the index SKULLCANDY_NAME has been removed before using the SHOW INDEXES command.

Also, take a look at some more MariaDB tutorials.

• MariaDB COUNT Function
• MariaDB Date Add Days
• MariaDB Rename Index
• MariaDB Rename Table
• MariaDB Not Between
• MariaDB Date_Format

In this MariaDB tutorial, we have discussed the MariaDB DROP INDEX statement and also discusses some sample examples related to it.