MariaDB Rename Index [With 6 Useful Examples]

In this MariaDB tutorial, we will look at how to utilize the MariaDB Rename Index statement and also discuss several examples related to it. There are lists of the topic that comes under discussion:

• MariaDB Rename Index
• MariaDB Rename Index If Exists
• MariaDB Rename Index Foreign Key
• MariaDB Rename Index Name
• MariaDB Rename Primary Index
• MariaDB Rename Unique Index

MariaDB Rename Index

We will learn how to rename the index of a column from a table in MariaDB, which will be described using syntax and examples.

In MariaDB, an index is a quality method for retrieving records faster. For each value that shows in the indexed columns, an index produces an entry. Indexes can be created in one of two ways.

During the creation of a table, you can use the CREATE TABLE statement or after the table has been created, you can use the CREATE INDEX statement.

First, we see the records of the EMPLOYEE table by the following query:

SELECT * FROM EMPLOYEE;

In the case of the EMPLOYEE table, all records are retrieved by the SELECT statement on MariaDB.

The syntax to create an index from the EMPLOYEE table by the following query:

SYNTAX to CREATE AN INDEX:

CREATE INDEX INDEX_NAME 
ON TABLE_NAME (COLUMN_NAME);

The syntax to rename the index of a column in the MariaDB is given below:

SYNTAX:

ALTER TABLE TABLE RENAME INDEX INDEX_NAME TO NEW_INDEX_NAME;

The syntax explanation:

• With the help of the RENAME keyword, we have to change the index_name of the table from old index_name to new index_name which is done with the help of the ALTER TABLE statement.

The example is to rename the index for the new index_name in the EMPLOYEE table by the following query:

EXAMPLE:

CREATE INDEX FIRSTNAME
ON EMPLOYEE(FIRST_NAME);

ALTER TABLE EMPLOYEE RENAME FIRSTNAME TO PERSON_FIRSTNAME;

SHOW INDEX FROM EMPLOYEE;

In the first query, we have created an index as FIRSTNAME on the FIRST_NAME column from the EMPLOYEE table by using the CREATE INDEX statement. Due to some reason the user wanted to change the index_name as FIRSTNAME of the FIRST_NAME column from the EMPLOYEE table.

In the second query, we have changed the index_name as FIRSTNAME to PERSON_FIRSTNAME of the FIRST_NAME column in the EMPLOYEE table by using the ALTER TABLE statement.

If we want to check the new index_name has been created or not for that column then use the SHOW INDEX statement. Please remember this statement is upgraded and can be used in MariaDB 10.5 or above version.

Read: MariaDB JSON Function

MariaDB Rename Index If Exists

In this section, we will learn how to rename the index in MariaDB with the IF EXISTS clause and we will explain it with the help of syntax and an example.

When the MariaDB IF EXISTS condition is used with a subquery, it is said to be “met” if the subquery produces at least one record. In a SELECT, INSERT, UPDATE, or DELETE statement, it can be utilized. The syntax to rename the index with the IF EXISTS clause is given below:

ALTER TABLE TABLE_NAME [IF EXISTS] RENAME INDEX TO NEW_INDEX_NAME;

The syntax explanation:

• First, the IF EXISTS clause will check that the index_name already exists for that table_name then it will drop the index_name by using the ALTER TABLE statement.
• Then we will change the old_indexname to new_indexname by using the ALTER TABLE statement with the help of RENAME keyword in the query.

The sample example to rename index_name with the IF EXISTS clause is given below:

EXAMPLE:

ALTER TABLE EMPLOYEE IF EXISTS RENAME INDEX LASTNAME TO EMP_LASTNAME;

In the preceding query, with the help of the IF EXISTS clause, it will check the index_name has been changed or not but if it already existed or changed it will show a warning. Then, it will change from the LASTNAME to EMP_LASTNAME as the new_indexname by using the ALTER TABLE statement of the EMPLOYEE table.

Read: MariaDB vs Postgres – Detailed Comparison

MariaDB Rename Index Foreign Key

In this section, we will learn how to rename the index of the foreign key column in the MariaDB table and which is explained with the help of an example.

First, let’s have a look at the EMPLOYEE table by the following query:

SELECT * FROM EMPLOYEE;

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

Now, let’s create a second table COMPANY table by the following query:

create table COMPANY (
company_id INT AUTO_INCREMENT PRIMARY KEY,
emp_firstname VARCHAR(50),
emp_lastname VARCHAR(50),
email VARCHAR(50),
gender VARCHAR(50),
emp_id INT,
CONSTRAINT fk_emp_id 
FOREIGN KEY (emp_id)
REFERENCES employee(emp_id));
	
	
insert into COMPANY (emp_firstname, emp_lastname, email, gender) VALUES 
('Davis', 'Serjeant', 'dserjeant0@bizjournals.com', 'Male'),
('Gipsy', 'Pranger', 'gpranger1@harvard.edu', 'Female'),
('Brady', 'Geator', 'bgeator2@china.com.cn', 'Bigender'),
('Arlie', 'McCrystal', 'amccrystal3@imageshack.us', 'Female'),
('Sayer', 'Redit', 'sredit4@blog.com', 'Bigender'),
('Bethann', 'Dibner', 'bdibner5@technorati.com', 'Female'),
('Ichabod', 'Gilbody', 'igilbody6@slideshare.net', 'Male'),
('Toby', 'Roadhead', 'treadhead7@tinyurl.com', 'Male'),
('Fayre', 'Walworth', 'fwalworche8@utexas.edu', 'Female'),
('Prescott', 'Zarfati', 'pzarfati9@oakley.com', 'Male');

SELECT * FROM COMPANY;

The CREATE INDEX command was used to create a new table COMPANY in the above query. Then, using the INSERT INTO statement, we added new records to the COMPANY table. The SELECT query is used to fetch all records from the COMPANY table.

The sample example to rename the index of the foreign key column of the COMPANY table is given below:

EXAMPLE:

CREATE INDEX CMP_ID ON company(COMPANY_ID); 

ALTER TABLE company RENAME INDEX CMP_ID TO VALID_COMPANY_ID;

In the first query, we have created a new index on the COMPANY_ID column as CMP_ID by using the CREATE INDEX statement. Then we wanted to change the INDEX_NAME from old to new as CMP_ID to VALID_COMPANY_ID from the COMPANY table by using the ALTER TABLE statement.

If we want to check the new index_name of the foreign key column as COMPANY_ID in the COMPANY table, then we use the SHOW INDEX statement.

Read: MariaDB Drop Table + Examples

MariaDB Rename Index Name

In this section, we will learn how to rename index_name and which is explained with the help of the following query:

EXAMPLE:

ALTER TABLE EMPLOYEE RENAME LASTNAME TO EMP_LASTNAME;

In the preceding query, we have renamed the index_name as LASTNAME to new_indexname as EMP_LASTNAME by using the ALTER TABLE statement of the EMPLOYEE table.

Read: MariaDB Foreign Key + Examples

MariaDB Rename Primary Index

In this section, we will learn how to rename the index of the PRIMARY KEY column by using the ALTER TABLE statement in MariaDB.

A primary key in MariaDB is a field that helps to uniquely specifies a table record. A primary key field cannot hold NULL value. Moreover, a table in MariaDB can have a maximum of one primary key field. The syntax to rename the index of the primary key column by the following query:

SYNTAX:

ALTER TABLE TABLE_NAME RENAME INDEX INDEX_NAME TO NEW_INDEX_NAME;

The sample example is to rename the primary index_name by the following query:

EXAMPLE:

ALTER TABLE EMPLOYEE RENAME INDEX EMPID TO EMPLOYEE_ID;

In the preceding query, we have used the RENAME keyword to change the index_name as EMP_ID to new_index_name as EMPLOYEE_ID from the EMPLOYEE table by using the ALTER TABLE statement.

Read: MariaDB Backup Database

MariaDB Rename Unique Index

In this section, we will learn how to rename a unique index in MariaDB and which is explained with the help of an example.

First, let’s have a look at the STATES_OF_USA table by the following query:

SELECT * FROM STATES_OF_USA;

In MariaDB, the SELECT statement retrieves all records from the STATES_OF_USA table.

The sample example to rename a unique index by using the ALTER TABLE statement is given below:

EXAMPLE:

CREATE INDEX STATE_SHORTNAME ON STATES_OF_USA(STATE_SHORTFORM);

ALTER TABLE STATES_OF_USA RENAME INDEX STATES_SHORTNAME TO STATES_NICKNAME;

In the first query, we have created the new index as STATE_SHORTNAME of the STATE_SHORTFORM column from the STATES_OF_USA table by using the CREATE INDEX statement.

In the second query, the user wanted to change the index_name from old to new due to some reason. So, for that we have used the ALTER TABLE statement with the RENAME keyword we have changed the index_name as STATE_SHORTNAME to new_index_name as STATES_NICKNAME from the STATES_OF_USA table.

If we want to check the new index_name has been updated or not then use the SHOW INDEX statement. This statement will provide all details related to the indexes of the table.

Also, take a look at some more MariaDB tutorials.

• MariaDB Order By Clause
• MariaDB Alter Table Compound Primary Key
• MariaDB COUNT Function
• MariaDB Join with Examples
• MariaDB Select Statement
• MariaDB Date_Format
• MariaDB Row_Number Tutorial
• MariaDB Rename View

In this MariaDB tutorial, we have discussed the “MariaDB Rename Index” and looked at some examples related to it. There are lists of the topic that comes under discussion:

• MariaDB Rename Index
• MariaDB Rename Index If Exists
• MariaDB Rename Index Foreign Key
• MariaDB Rename Index Name
• MariaDB Rename Primary Index
• MariaDB Rename Unique Index