MySQL RENAME Table

Introduction

In this chapter, we will learn how to rename a table in MySQL. Renaming a table can be useful when you want to change the table name to something more meaningful or to align with naming conventions. We will cover the syntax and provide examples of renaming tables.

Renaming a Table

To rename a table, we use the RENAME TABLE statement. This command changes the name of an existing table to a new name.

Syntax

RENAME TABLE old_table_name TO new_table_name;

• old_table_name: The current name of the table you want to rename.
• new_table_name: The new name for the table.

Example

RENAME TABLE employees TO staff;

This example renames the table employees to staff.

Full Example

Let's go through a full example where we create a database, create a table, rename the table, and then verify the renaming.

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create a Table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

4. Rename the Table:

RENAME TABLE employees TO staff;

5. Verify the Table Renaming:

SHOW TABLES;

Output

Tables_in_company
staff

Important Considerations

• Dependencies: Make sure to update any references to the old table name in your queries, stored procedures, and application code.
• Permissions: You need the appropriate privileges to rename a table. Typically, this requires ALTER and DROP privileges on the table.
• Locking: Renaming a table may lock the table for a brief period. Ensure that this operation does not interfere with ongoing transactions or operations.

Conclusion

Renaming a table in MySQL is a straightforward process using the RENAME TABLE statement. This chapter covered how to rename a table and provided a full example to illustrate the process.