MariaDB Drop Index [7 Useful Examples]

In this MariaDB tutorial, we will look at the MariaDB Drop Index statement and look at several examples. There are lists of the topic that comes under discussion:

• MariaDB Drop Index
• MariaDB Drop Index Primary Key
• MariaDB Drop Index If Exists
• MariaDB Alter Table Drop Index If Exists
• MariaDB Drop Index By Name
• MariaDB Cannot Drop Index Needed in a Foreign Key Constraint
• MariaDB Drop Unique Index

MariaDB Drop Index

In this section, we will learn how to drop the index using a SQL query in MariaDB and it is explained with the help of an illustrated example.

In MariaDB, the DROP INDEX statement is used to remove 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 which is used to remove the index remove 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 show all indexes from the STATES_OF_USA table by using the SHOW INDEXES statement.

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. And we will explain it with the help of 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 an index called STATESNAME from the STATES_OF_USA table by 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. And it is explained with the help of syntax and an illustrated example.

The MariaDB ALTER TABLE statement is used to modify, add, and delete columns from the table. It is also used to rename columns from the table. The MariaDB index is an achievement method for retrieving records faster. For each value that occurs 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 have 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 true.
• Then it will drop the index name called STATESPOPULATION from the STATES_OF_USA table by 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 is used to retrieve 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 drop the index called USA_PERSONNAME from the CUSTOMER_LIST table by using the DROP INDEX statement.

And in the second query, if we want to verify the USA_PERSONNAME index has been removed properly from the CUSTOMER_LIST table then 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 and duplicate value. The Foreign Key constraint column contains in the child table whereas the foreign key which is referred to as the Primary Key column contained 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 have created the index called SKULLCANDYID on the SKULLCANDY_ID column from the SKULLCANDY table by using the CREATE INDEX statement.
• If we use the SHOW INDEXES statement, it will show the index that we have created called SKULLCANDYID from the SKULLCANDY_ID column from the SKULLCANDY table.

Here is the 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 made understandable that we have first disabled the foreign_key_checks as 0.
• Then by using the ALTER TABLE statement, we have dropped the index called SKULLCANDYID from the SKULLCANDY table.
• After that, we set again the foreign_key_checks to enabled so that the index of the foreign key constraint will be still tied to the parent table.
• In the last query, we have 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 drop the unique index key in the MariaDB table and we will explain it with the help of 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 to drop 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;

We used the DROP INDEX statement to remove the index called SKULLCANDY_NAME from the SKULLCANDY table, as seen in the first query example. In the second query, we must determine whether or not 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. There are lists of the topic that comes under discussion:

• MariaDB Drop Index
• MariaDB Drop Index Primary Key
• MariaDB Drop Index If Exists
• MariaDB Alter Table Drop Index If Exists
• MariaDB Drop Index By Name
• MariaDB Cannot Drop Index Needed in a Foreign Key Constraint
• MariaDB Drop Unique Index