MariaDB Rename Table [With 8 real examples]

In this MariaDB tutorial, we will understand how to rename a table in MariaDB and look at several examples. There are lists of the topic that comes under discussion:

• MariaDB rename table
• MariaDB rename table if exists
• MariaDB rename table column
• MariaDB columnstore rename table
• MariaDB alter table rename index
• MariaDB alter table rename column example
• MariaDB rename all tables to lowercase
• MariaDB rename all tables to uppercase

MariaDB Rename Table

Here, we’ll learn how to use the MariaDB rename table in this part, which is explained using syntax and examples.

The ALTER TABLE statement in MariaDB is used to change, drop, or delete data from the columns of a table. Renaming columns in a table can also be done with the MariaDB ALTER TABLE statement. The ALTER TABLE statement has the following syntax for renaming a table:

SYNTAX:

ALTER TABLE OLD_TABLE_NAME RENAME TO NEW_TABLE_NAME;

The syntax explanation:

• OLD_TABLE_NAME: the change of name of the table to new table_name by using the ALTER TABLE statement.
• NEW_TABLE_NAME: the change into new name of the table from old table.

The following is an example of how to rename table_name using the ALTER TABLE statement:

EXAMPLE:

ALTER TABLE USA_STATES RENAME TO STATES_OF_USA;

SHOW TABLES;

The MariaDB ALTER TABLE statement is used in the above query to change the table name from USA_STATES to the STATES_OF_USA by using the RENAME clause.

So, as the result set, it will permanently change the table_name from USA_STATES to STATES_OF_USA. The SHOW TABLES statement can be used to see if the table name has been modified or not.

Also, check: MariaDB Rename Column

MariaDB Rename Table If Exists

In this section, we’ll learn how to utilize the IF EXISTS clause to rename a table that already exists in the MariaDB database, including syntax and examples.

The IF EXISTS condition in MariaDB is used in conjunction with a subquery and is regarded “met” when the subquery returns at least one record. In a SELECT, INSERT, UPDATE, or DELETE statement, it can be utilized.

The following is the syntax for renaming table_name using the ALTER TABLE command with the IF EXISTS clause:

SYNTAX:

ALTER TABLE RENAME OLD_TABLE_NAME [IF EXISTS]
TO NEW_TABLE_NAME;

The syntax explanation:

• IF_EXISTS clause: The main purpose of IF EXISTS clause is that it will only change the table_name only when that table already existed in the database.

The sample example to rename table_name with the help of the IF EXISTS clause in the ALTER TABLE statement is given below:

EXAMPLE:

ALTER TABLE RENAME IF EIXSTS USA_STATES TO STATES_OF_USA;

In the above query, we have used the IF EXISTS clause in the ALTER TABLE statement to change the old_table_name as USA_STATES to new_table_name as STATES_OF_USA.

As a result, it will use the IF EXISTS clause to determine whether the table’s name has changed. It will throw an error if it has already been altered.

Read: MariaDB JSON Function + Examples

MariaDB Rename Table Column

Here we will learn how to rename the table’s column name using the MariaDB ALTER TABLE statement. And we will also illustrate its syntax and an example for better understanding.

The following is the syntax for renaming a table column using the ALTER TABLE statement in MariaDB:

SYNTAX:

ALTER TABLE TABLE_NAME
CHANGE COLUMN OLD_COLUMN_NAME NEW_COLUMN_NAME COLUMN_DEFINITION
[ FIRST | AFTER COLUMN_NAME];

The syntax explanation:

• TABLE_NAME: The name of the table that we want to modify
• OLD_COLUMN_NAME: The column to rename.
• NEW_COLUMN_NAME: The new name of the column.
• COLUMN_DEFINITION: The definition of the column’s datatype and datatype (NULL or NOT NULL, etc). When renaming a column, even if it does not change, you must specify the column definition.
• [FIRST | AFTER COLUMN_NAME]: It is optional and it tells MariaDB where in the table to position the column, if you want to change its position.

The following is an example of how to use the ALTER TABLE statement to rename a table column:

EXAMPLE:

ALTER TABLE STATES_OF_USA CHANGE COLUMN
STATE_CODE STATE_SHORTFORM VARCHAR(10);

DESC STATES_OF_USA;

The MariaDB ALTER TABLE statement will rename column_name from STATE_CODE to STATE_SHORTFORM with datatype as VARCHAR(10) which allows NULL values. We can use the DESC STATES_OF_USA command to see if the table’s column name has been modified or not.

Read: MariaDB Drop Table + Examples

MariaDB Columnstore Rename Table

In this part, we’ll look at how to rename a table in MariaDB’s columnstore using the ALTER TABLE statement. And we will also illustrate the use of its syntax using an example.

The ALTER TABLE statement in MariaDB is used to make changes to existing tables. It also enables renaming tables and adding, removing, and renaming columns. The following is the syntax for using the ALTER TABLE statement to columnstore rename a table:

SYNTAX:

ALTER TABLE TABLE_NAME 
RENAME COLUMN_NAME TO NEW_COLUMN_NAME;

The following is an example of how to use the ALTER TABLE statement to rename a table in columnstore:

EXAMPLE:

ALTER TABLE STATES_OF_USA
RENAME STATE_NAME TO NAMES_OF_STATE;

In the above query, we have renamed column_name from the STATE_NAME column to the NAMES_OF_STATE column in the STATES_OF_USA table by using the ALTER TABLE statement.

Read: MariaDB Foreign Key + Examples

MariaDB Alter Table Rename Index

In this section, we’ll look at how to rename an index in MariaDB with the ALTER TABLE statement, which is demonstrated with syntax and an example.

The MariaDB table index is just like a book index that helps to find information quicker and easier. First, we have to create an index on the STATES_OF_USA table by the following query:

SYNTAX:

CREATE INDEX INDEX_NAME 
ON TABLE_NAME (COLUMN_NAME);

EXAMPLE:

CREATE INDEX STATES
ON STATES_OF_USA(STATE_SHORTFORM);

As we see in the above query, we have created index_name as STATES index_column on the STATE_SHORTFORM column of the STATES_OF_USA table by using the CREATE INDEX statement.

The following is an example of how to rename an index using the ALTER TABLE statement:

EXAMPLE:

ALTER TABLE STATES_OF_USA 
RENAME INDEX STATES TO SHORTFORM_STATES;

As we see in the above query, we have renamed the index_name from STATES to SHORTFORM_STATES in the STATES_OF_USA table by using the ALTER TABLE statement.

Also, check: MariaDB Temporary Table + Examples

MariaDB Alter Table Rename Column Example

In this sub-topic, we’ll learn how to rename a column in a table using the ALTER TABLE statement in MariaDB, which will be demonstrated with examples.

Let’s have a look at the STATES_OF_USA table by the following query:

SELECT * FROM STATES_OF_USA;

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

The following is an example of how to use the ALTER TABLE statement to rename a column in a table:

ALTER TABLE STATES_OF_USA 
CHANGE COLUMN STATE_POLLUTION STATE_POPULATION ENUM('HIGH','MEDIUM','LOW');

DESC STATES_OF_USA;

As we see in the above query, we have used the ALTER TABLE statement to change column_name from STATE_POLLUTION to STATE_POPULATION with data type VARCHAR(10).

So, if we want to see if the table’s column_name has changed, we can use the DESC statement to describe the column name of the STATES_OF_USA table or the DESC STATES_OF_USA statement.

Read: MariaDB Check Empty String

MariaDB Rename All Tables to Lowercase

In this section, we’ll learn how to rename all tables in MariaDB and change their case to lowercase, which will be explained with syntax and examples.

We may do this in MariaDB by using the INFORMATION SCHEMA.COLUMNS table. The following is the syntax:

SELECT CONCAT('RENAME TABLE`',TABLE_NAME,'`TO`',LOWER(TABLE_NAME),'`;') 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_name';

The sample example to MariaDB rename all tables to lowercase as given below:

EXAMPLE:

SELECT CONCAT('RENAME TABLE`',TABLE_NAME,'`TO`',LOWER(TABLE_NAME),'`;') 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'airbnb_db';

As we see in the above query, we have just added the database_name as airbnb_db from my computer and it changed all tables to lower_case in the query.

Read: MariaDB Truncate Table + Examples

MariaDB Rename All table to Uppercase

In this sub-topic, we’ll learn how to rename all tables in MariaDB and change their case to UPPERCASE using syntax and examples.

We may do this in MariaDB by using the INFORMATION SCHEMA.COLUMNS table. The following is the syntax:

SELECT CONCAT('RENAME TABLE`',TABLE_NAME,'`TO`',UPPER(TABLE_NAME),'`;') 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_name';

The sample example to MariaDB rename all tables to the UPPERCASE as given below:

EXAMPLES:

SELECT CONCAT('RENAME TABLE`',TABLE_NAME,'`TO`',UPPER(TABLE_NAME),'`;') 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'airbnb_db';

As we see in the above query, we have just added the database_name as airbnb_db from my computer and it changed all tables to upper_case in the query.

You may also like to read the following MariaDB tutorials.

• MariaDB Transaction
• MariaDB Alter Table If Exists
• MariaDB Alter Table Add Index
• MariaDB Max Connections
• MariaDB Select Statement
• MariaDB Order By Clause
• MariaDB Rename View
• MariaDB Reserved Words
• Change Column in MariaDB

In this MariaDB tutorial, we have discussed the implementation of the MariaDB Rename Table and look at some sample examples. There are lists of the topic that comes under discussion:

• MariaDB rename table if exists
• MariaDB rename table column
• MariaDB columnstore rename table
• MariaDB alter table rename index
• MariaDB alter table rename column example
• MariaDB rename all tables to lowercase
• MariaDB rename all tables to uppercase