How to Rename Table in MySQL? – Pencil Programmer

Summary: In this post, we will learn how to rename an existing table using the MySQL RENAME TABLE statement in MySQL.

Introduction to RENAME TABLE

The general syntax to rename a table in MySQL is:

RENAME TABLE old_table TO new_table;

We can also rename a table using the ALTER TALBE statement:

ALTER TABLE old_table RENAME new_table;

If you need to delete multiple tables with single SQL statement then use the following RENAME TABLE statement:

RENAME TABLE old_table1 TO new_table1,
             old_table2 TO new_table2,
             old_table3 TO new_table3;

ALTER TABLE, unlike RENAME TABLE, cannot rename multiple tables within a single statement.

Also, it is important to note the following points while renaming any table in MySQL

We cannot rename a temporary table in MySQL using the RENAME TALBE statement but we can do so using the ALTER TABLE statement.
To rename the table we must have ALTER and DROP privileges for the original table, and CREATE and INSERT privileges for the new table.
We can also rename views using RENAME TABLE, except that views cannot be renamed into 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.
Any database objects such as views, stored procedures, triggers, foreign key constraints, etc., that was referencing the old table are automatically updated.

Let’s see some examples for better understanding.

In our examples, we will use the ‘pencil_db’ database that currently has the following tables.

Example 1: Renaming table ‘programming’ into ‘coding’ using RENAME TABLE statement.

RENAME TABLE programming TO coding;

Example 2: Renaming table ‘shirts’ into ‘top_wear’ using ALTER TABLE statement.

ALTER TABLE shirts RENAME top_wear;

Example 3: Renaming tables ‘coding’ and ‘top_wear’ back to their old names using single RENAME TABLE statement.

RENAME TABLE 
coding TO programming, 
top_wear TO shirts;

Example 4: Renaming a table with FOREIGN KEY constraint.

Adding the FOREIGN KEY constraint to the ‘programming’ table.

ALTER TABLE programming 
ADD CONSTRAINT fk_student_roll 
FOREIGN KEY (roll_no) REFERENCES students(roll_no);

Query OK, 1 row affected (3.04 sec)

The ‘programming.roll_no’ is now referencing to ‘students.roll_no’. Now let’s change the ‘student’ table name and observe what happens.

RENAME TABLE students TO users;

Query OK, 0 rows affected (1.07 sec)

The SQL query is successful.

To make sure that FOREIGN KEY constraint was automatically updated by MySQL we will use the SHOW CREATE TABLE command on ‘programming’ table.

As you can see, the table ‘programming’ is referencing the changed named i.e. ‘users’.

The best practice of renaming any table in MySQL is surrounding the table names with backticks.

RENAME TABLE `old_table` TO `new_table`;

Because in some cases the table name may conflict with MySQL keywords and MySQL will interpret them as a command rather than a table name.

Example:

RENAME TABLE group TO team;

ERROR 1064 (42000): You have an error in your SQL syntax;

To make sure, MySQL interprets ‘group’ as a table name surround it with backticks (`).

RENAME TABLE `group` TO `team`;

Query OK, 0 rows affected (0.79 sec)

Note: Surround table name with backticks (`) not with single quotes (‘).

In this tutorial, we have learned to rename a table in MySQL using the RENAME TABLE SQL query.