MySQL RENAME TABLE Statement with Examples


How to Rename Table in MySQL

In this tutorial, you'll learn how to rename a table in MySQL. To rename a table in MySQL, you can use the RENAME TABLE statement.

RENAME TABLE Syntax

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

RENAME TABLE old_table_name TO new_table_name;

You also can rename multiple tables at once using the following statements:

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 new_table_name must not exist in a given database.
  • You also can 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 suppliers table must not, otherwise MySQL issues an error.

Example 2: Rename Two Tables

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

RENAME TABLE

vendors TO suppliers,

staffs TO employees;

Example 3

In this example, suppose you have two tables, customers and groups. The customers table consists of three columns, id, customer_name, and group_id. And the groups table consists of two columns, id and group_name. The groups table links to the customers table using the group_id column. The group_id column in the customer table is a foreign key that references the groups table.

First, let's create the groups table as follows:

CREATE TABLE groups (

id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,

group_name VARCHAR(40) NOT NULL);

Then create the customers table 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 customers to employees table using the following statement:

RENAME customers TO employees;

Now, if you check the foreign key of the employee table, the name of the foreign key constraint is still the same. In this case, you need to drop and then recreate the foreign key manually.

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

Leave a Reply

Your email address will not be published. Required fields are marked *