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
DROPprivileges for the original table, and
INSERTprivileges 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.
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.
FOREIGN KEY constraint to the ‘programming’ table.
Query OK, 1 row affected (3.04 sec)
ALTER TABLE programming ADD CONSTRAINT fk_student_roll FOREIGN KEY (roll_no) REFERENCES students(roll_no);
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)
RENAME TABLE students TO users;
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’.
Best Prcatice of Renaming a table in MySQL
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.
ERROR 1064 (42000): You have an error in your SQL syntax;
RENAME TABLE group TO team;
To make sure, MySQL interprets ‘group’ as a table name surround it with backticks (`).
Query OK, 0 rows affected (0.79 sec)
RENAME TABLE `group` TO `team`;
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.