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:

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

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

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.

MySQL RENAME TABLE examples

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

pencil_db database tables

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

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

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

pencil_db database tables

Example 4: Renaming a table with FOREIGN KEY constraint.

Foreign key in MySQL

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

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.

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.

show create table programming

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

Best Prcatice of Renaming a table in MySQL

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

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:

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 (`).

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.

Leave a Reply