Previous
Written by: rJTutorial Team | Last Updated:

SQL RENAME TABLE

The RENAME TABLE statement is a fundamental DDL (Data Definition Language) command used to change the name of an existing table in your database. As projects evolve, initial naming conventions might no longer fit your data structure, or you might need to swap tables during a data migration process. This command allows you to update your schema without losing any of your stored data.

Developer Tip: Renaming a table is much faster and more efficient than creating a new table and moving data into it, as it primarily updates the database's internal metadata.

 

Key Features of SQL RENAME TABLE

  • Data Integrity: Changing the name does not affect the rows inside the table; all data remains exactly as it was.
  • Metadata Preservation: Indexes, constraints (like Primary Keys or Foreign Keys), and column definitions are automatically carried over to the new name.
  • Multi-Table Support: Some engines, like MySQL, allow you to rename multiple tables in a single atomic transaction.
  • Database Specifics: While the concept is universal, the exact syntax varies between MySQL/MariaDB and other systems like PostgreSQL or SQL Server.
Watch Out: Renaming a table is a "breaking change." Any application code, API endpoints, or scripts that reference the old table name will fail immediately after the rename.

 

SQL RENAME TABLE Syntax

In MySQL and MariaDB, the syntax is straightforward. You specify the current table name followed by the desired new name.

RENAME TABLE old_table_name TO new_table_name;
Best Practice: Always check for active database connections or long-running transactions before renaming a table, as this command usually requires an exclusive lock on the table.

 

Example: Renaming a Table

Imagine you have a table named Employees, but your HR department has requested it be changed to Staff to better reflect the company culture.

RENAME TABLE Employees TO Staff;
  • This command instantly renames the Employees table to Staff.
  • Any existing records, such as employee IDs and salaries, remain untouched.
Common Mistake: Forgetting to update Stored Procedures or Views that reference the old table. SQL does not always automatically update these dependencies when a table name changes.

 

Example: Renaming Multiple Tables

A unique feature of the RENAME TABLE syntax in MySQL is the ability to rename several tables at once. This is particularly useful when performing a batch update or versioning your schema.

RENAME TABLE Orders TO CustomerOrders, Products TO Inventory;
  • Changes Orders to CustomerOrders and Products to Inventory in one go.
  • The operation is atomic: if any single rename fails, none of the renames in the statement will take effect.

Alternative Commands for Other Databases

If you are working with databases other than MySQL or MariaDB, the RENAME TABLE command might not work. Instead, most other SQL dialects use the ALTER TABLE statement.

SQL Commands to Rename a Table

Database Command
MySQL / MariaDB RENAME TABLE old_table_name TO new_table_name;
PostgreSQL ALTER TABLE old_table_name RENAME TO new_table_name;
SQL Server EXEC sp_rename 'old_table_name', 'new_table_name';
Oracle ALTER TABLE old_table_name RENAME TO new_table_name;
Developer Tip: In SQL Server, sp_rename is a stored procedure. Note that the table names should be wrapped in single quotes within the parentheses.

 

Summary

  • Primary Use: RENAME TABLE is the standard way to rename tables in MySQL and MariaDB.
  • Dialect Variation: Most other systems (PostgreSQL, Oracle, SQLite) use ALTER TABLE ... RENAME TO ....
  • Safety: Data, indexes, and constraints are preserved, but external dependencies (like app code) must be updated manually.
  • Bulk Operations: MySQL allows renaming multiple tables simultaneously, ensuring an "all or nothing" result.
Previous