MariaDB Rename Index [With 6 Useful Examples]

In this MariaDB tutorial, we will examine how to utilize the MariaDB Rename Index statement and discuss several related examples.

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. An index produces an entry for each value in the indexed columns. Indexes can be created in one of two ways.

During the creation of a table, you can use the CREATE TABLE statement; 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:

• The RENAME keyword helps us change the table’s index_name from the old index_name to the 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 created an index as FIRSTNAME on the FIRST_NAME column from the EMPLOYEE table using the CREATE INDEX statement. However, the user wanted to change the index_name to FIRSTNAME of the FIRST_NAME column from the EMPLOYEE table.

In the second query, we used the ALTER TABLE statement to change the index_name from FIRSTNAME to PERSON_FIRSTNAME of the FIRST_NAME column in the EMPLOYEE table.

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

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 explain it using 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. A SELECT, INSERT, UPDATE, or DELETE statement 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 checks that the index_name already exists for that table_name. Then, it drops 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 and the 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, the IF EXISTS clause checks whether the index_name has been changed. If it already exists or has been changed, it shows a warning. Then, it changes 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, which is explained with 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 created 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 used the ALTER TABLE statement of the EMPLOYEE table to rename the index_name from LASTNAME to new_indexname as EMP_LASTNAME.

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 specify a table record uniquely. A primary key field cannot hold a 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 used the RENAME keyword to change the index_name from EMP_ID to new_index_name as EMPLOYEE_ID from the EMPLOYEE table 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, which is explained with 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.

A sample example of renaming 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 created the new index as STATE_SHORTNAME of the STATE_SHORTFORM column from the STATES_OF_USA table using the CREATE INDEX statement.

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

If we want to check whether the new index_name has been updated, we can use the SHOW INDEX statement. This statement will provide all details related to the table’s indexes.

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 discussed the “MariaDB Rename Index” and looked at some related examples.