MySQL RENAME TABLE Statement with Examples

How to Rename a Table in MySQL

In this tutorial, you will learn how to rename a table using the MySQL RENAME TABLE statement.

RENAME TABLE Syntax

To rename a table in MySQL, use the following statement:

RENAME TABLE old_table_name TO new_table_name;

You can also rename multiple tables at once with the following statement:

RENAME TABLE
     old_table_name1 TO new_table_name1,
     old_table_name2 TO new_table_name2,
     ...
old_table_nameN TO new_table_nameN;

Remarks

The old_table_name must exist, and the new_table_name must not exist in the given database.
You can also use the ALTER TABLE statement to rename a table.

Examples

Example 1: Rename a single table

The following statement renames the vendors to suppliers table:

RENAME TABLE vendors TO suppliers;

The "vendors" table must exist, and the "suppliers" table must not; otherwise, MySQL will issue an error message.

Example 2: Rename two tables

The following statement renames the vendors and staffs tables to the supplier and employees tables:

RENAME TABLE
vendors TO suppliers,
staffs TO employees;

Example 3

In this example, suppose we have two tables: customers and groups. The "customers" table consists of three columns: id, customer_name, and group_id; the "groups" table consists of two columns: id and group_name. The group_id column of the "customers" table is a foreign key that references to the id column of the "groups" table.

First, create a table named groups as shown below:

CREATE TABLE groups (
id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
group_name VARCHAR(40) NOT NULL);

Then, create a table named customers as follows:

CREATE TABLE customers (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
group_id SMALLINT NULL,
CONSTRAINT fk_customers_groups_group_id FOREIGN KEY (group_id) REFERENCES groups (id));

Next, let's rename the table name from "customers" to "employees," as shown below.

RENAME customers TO employees;

If you check the foreign key in the "employees" table, the foreign key constraint still has the same name. In this case, you need to drop the foreign key manually and then recreate it.

Summary

In this tutorial, you have learned how to rename a table using the MySQL RENAME TABLE statement.