Change Table Name In MySQL

A unique name is assigned to each table in the schema. In MySQL, there are two methods to determine the names of the tables in the schema. The user can either use the show keyword to display the table's names or the query INFORMATION_SCHEMA.

Naming A Table In MySQL

The user can use capital and small alphabets when naming database objects. Using all small letters while naming and underscore separating the words in the database object name is preferred. The name of the table can both be singular or can be plural. However, following a particular convention in a particular schema is better.
If your table stores information about real-world entities that represent the characteristics of real-world entities, then the name should be a noun. Examples of these tables are student, employee, customer, and city. It is better to describe the data stored in the table. This makes it easier for other users to understand the database and data stored in the table.
If you want to use multiple words to describe the name of the table and the data stored in it, then you can do so. Consider an example that contains information about the selling prices of different items. The user can simply use selling_price as the table's name, especially when the user has already used the price for another table in the database. Moreover, if the user only uses selling as the table, it will be difficult for other users to determine what data is stored there. Therefore, the name selling_price is better suited for the above scenario as it provides a clear picture of its information.
Suppose the table represents the relation between two tables of the same database using foreign keys. In that case, it is better to use the name of these two tables with a verb to describe their relationship.
Suppose that the user has two tables, the first table stores information about different users, and the second table has the possible users that can be assigned to the users. Suppose the user wants to add many too many relations between these tables. Meaning that multiple users can have multiple rows and vice versa, then the table's name could be user_has_roles, or the user can remove the verb and use user_role for short.
The user can make an exception if there is a logical relation between the tables. For instance, if the user has two tables, product, and invoice, and wants a table that will specify which product is in which of the invoice, then the user can simply use the name invoice_product or invoice_contains_product. But it would be better to name invoice_item as it is more frequently used in the real world. The decision still depends on the user.

Changing the Name of an Existing Table

In several situations, DBA or the user may require to modify the table's name in MySQL database. This action is performed to assign a more relatable name to the particular table, mostly after adding a new column of information to the table.

There are two methods to change the name of an existing table in MySQL. Both these methods require the user to implement the RENAME query. In the first method, the RENAME TABLE command is implemented with ALTER table statement in MySQL.

Any database user with suitable permission can easily change the table's name using MySQL's RENAME TABLE command and ALTER TABLE statement.

USING RENAME TABLE STATEMENT

The syntax for using RENAME TABLE is as follows:

RENAME TABLE
    table_name TO new_table_name,
table_name2 TO new_table_name2 ..
table_nameN TO new_table_nameN;

The advantage of using RENAME TABLE is that the user can simultaneously change the name of multiple tables.

The user requires certain permission over the database to remain in the table. The user must have ALTER and DROP privileges for the existing table and CREATE and INSERT privileges for the new table.

For example, if the user wants to maintain an existing table with the name old_t to new_t, then the user can execute this statement:

The above statement can be replaced with an ALTER TABLE statement to change the name of the table:

RENAME TABLE statement is preferred over the ALTER TABLE because unlike RENAME TABLE, ALTER TABLE can only change the name of one table at a time. RENAME TABLE can change name of multiple table simultaneously.

RENAME TABLE old_t1 TO new_t1,
             old_t2 TO new_t2,
             old_t3 TO new_t3;

RENAME TABLE ensures more flexibility as the user can change the names of several tables by executing a single statement. It is more readable as the user can track the table whose name is changed. It is especially popular when modifying the name of a table with a new pre-populated version:

The above statement is executed from left to right. This means that the renaming starts from the left side then the next table name is changed. It ensures there is no conflict when changing the name of the existing table whose name was already changed in the same RENAME statement. The user does not need to concern themselves with this operation as it is done atomically.

It also enables the user to swap two tables' names. It could be done by simply creating a temporary table. Let us consider a scenario where the intermediary table with the name temp_table does not already exist:

Then the user can simply swap the names of the two tables by executing the following statement in MySQL:

RENAME TABLE old_t TO temp_table,
new_t TO old_t,
temp_t TO new_t;

Metadata locks on the table are acquired in the sequence of the name; this could be an issue as, in some scenarios, it may result in differences in operation outcomes when several transactions are implemented simultaneously.

In the latter versions of MySQL 8.0.13, the user can change the name of the locked table by using the LOCK TABLES statement. The only requisite was that the tables should have been locked with a WRITE lock or the product of modifying WRITE-LOCKED tables from previous steps in a multiple table RENAME operation. For instance, the user can implement this:

LOCK TABLE old_t1 WRITE;
RENAME TABLE old_t1 TO new_t1,
new_t1 TO new_t2;

The user cannot perform RENAME command in the below statements:

LOCK TABLE old_t1 READ;
RENAME TABLE old_t1 TO new_t1,
new_t1 TO new_t2;

In the previous versions of MySQL 8.0.13, the user could implement the RENAME TABLE; there should not be any lock already acquired by the LOCK TABLES.

With the locking conditions of the transaction table being met, the RENAME operation is performed atomically; this ensures that no other session can access any of the tables for which rename command is being implemented. If any error occurs during the execution of the RENAME TABLE, then the whole statement fails, and no modification is performed on the table.

The user can also implement RENAME TABLE to move the table from one database to another:

Using the above statement to move all the tables from one db to another, in effect, renames the name of the database. The original database is not modified, and it exists unchanged.

Like RENAME TABLE, ALTER TABLE ? NAME enables the user to transfer a table to a different database. It does not implement the query if the rename statement changes the table's location to a database on a different file system. Then the successful implementation of the operation depends upon the platform. It is determined by the underlying operating system calls used to transfer table files.
If triggers are implemented in the table, and the user tries to modify the table's name into another database, the attempt will fail with a trigger in the wrong schema. It will return the ER_TRG_IN_WRONG_SCHEMA error.
The user can change the location of an unencrypted table to an encrypted database or vice versa. But if the table_encryption_privilege_check variable is enabled, the user will need encryption admin privilege. This additional privilege is required because the encryption setting may differ from the default database encryption.
If the user wants to change the name of a temporary table, then the RENAME table command will not function. Instead, the user must implement ALTER TABLE command to modify the name.
RENAME TABLE can also be implemented to change the name of views, but the user cannot change the name of the view in a different database.
Any privileges granted specifically for a renamed table or view are not migrated to the new name. They must be changed manually.

Implementation of ALTER and RENAME Command

The user can use ALTER and RENAME commands to change the table's name one at a time.

-- create 
CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);

-- insert
INSERT INTO EMPLOYEE VALUES (01, 'Aman', 'Accounts');
INSERT INTO EMPLOYEE VALUES (02, 'Himani', 'Sales');
INSERT INTO EMPLOYEE VALUES (03, 'Vaibhav', 'Maintenance');

-- fetch 
SELECT * FROM EMPLOYEE;

--renaming the table using ALTER and Rename
ALTER TABLE EMPLOYEE RENAME NEWEMP;
SELECT * FROM NEWEMP;

Output:

The user can use the RENAME statement to change the names of multiple table by using a single command. Given below is an example query to implement RENAME query.

-- create 
CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);
CREATE TABLE STUDENT (
  studentId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);
-- insert
INSERT INTO EMPLOYEE VALUES (01, 'Aman', 'Accounts');
INSERT INTO EMPLOYEE VALUES (02, 'Himani', 'Sales');
INSERT INTO EMPLOYEE VALUES (03, 'Vaibhav', 'Maintenance');

INSERT INTO STUDENT VALUES (01, 'Aman', 'English');
INSERT INTO STUDENT VALUES (02, 'Himani', 'Mathematics');
INSERT INTO STUDENT VALUES (03, 'Vaibhav', 'Science');
-- fetch 
SELECT * FROM EMPLOYEE;
SELECT * FROM STUDENT;
-- renaming the table using ALTER and Rename
RENAME TABLE EMPLOYEE TO NEWEMP, STUDENT TO NEWSTU;
SELECT * FROM NEWEMP;
SELECT * FROM NEWSTU;

Output: