SQL

RENAME TABLE in SQL

🏷️ The RENAME TABLE statement is used to change the name of an existing table without affecting its data. Renaming a table is useful when improving naming conventions, restructuring a database, or replacing temporary table names with permanent ones.

📖 What is RENAME TABLE?

The RENAME TABLE operation changes only the table's name. The table's rows, columns, indexes, constraints, and stored data remain unchanged. Depending on the database system, the syntax for renaming a table may differ.

Information

Some databases use the RENAME TABLE statement, while others use ALTER TABLE ... RENAME TO or database-specific alternatives.

🎯 Why Use RENAME TABLE?

Renaming tables helps keep database schemas clear, organized, and easier to maintain.

📌 Improve table naming conventions.
📌 Replace temporary table names with permanent ones.
📌 Reflect changes in business requirements.
📌 Improve database readability.
📌 Simplify long or unclear table names.

📝 Basic Syntax

RENAME TABLE Syntax (MySQL)

RENAME TABLE old_table_name
TO new_table_name;

💡 Rename a Table

Rename the Students table to UniversityStudents.

Rename Students Table

RENAME TABLE Students
TO UniversityStudents;

After execution, the table is available using the new name UniversityStudents. All existing records remain intact.

🔄 Using ALTER TABLE to Rename

Many SQL database systems rename tables using the ALTER TABLE statement.

ALTER TABLE ... RENAME TO

ALTER TABLE Students
RENAME TO UniversityStudents;

Important

The exact syntax depends on the database system. Always verify the supported syntax for your DBMS before executing the statement.

📊 Before and After Renaming

Before	After
Students	UniversityStudents

📦 Rename Multiple Tables (MySQL)

MySQL allows multiple tables to be renamed in a single statement.

Rename Multiple Tables

RENAME TABLE
Students TO UniversityStudents,
Teachers TO Faculty;

📊 What Changes After Renaming?

Database Object	Result
Table Name	✅ Changed
Rows (Data)	✅ Preserved
Columns	✅ Preserved
Indexes	✅ Preserved
Constraints	✅ Preserved
Stored Data	✅ Preserved

💼 Real-World Example

A university upgrades its database naming standards. The table Students is renamed to StudentRecords to better reflect its purpose.

Rename Student Table

ALTER TABLE Students
RENAME TO StudentRecords;

⚖️ RENAME TABLE vs ALTER TABLE vs DROP TABLE

Feature	RENAME TABLE	ALTER TABLE	DROP TABLE
Rename Table	✅ Yes	✅ (Some DBMSs)	❌ No
Modify Structure	❌ No	✅ Yes	❌ No
Delete Table	❌ No	❌ No	✅ Yes
Preserves Data	✅ Yes	✅ Yes	❌ No

🗄️ Database Compatibility

Database System	Common Syntax
MySQL	RENAME TABLE or ALTER TABLE ... RENAME TO.
PostgreSQL	ALTER TABLE ... RENAME TO.
SQL Server	Typically uses the sp_rename stored procedure to rename tables.
Oracle	RENAME old_name TO new_name;.
SQLite	ALTER TABLE ... RENAME TO.

⚠️ Common Mistakes

❌ Renaming a table without updating application code.
❌ Choosing unclear or inconsistent table names.
❌ Renaming a table that is referenced by dependent objects without verifying the impact.
❌ Assuming every SQL database uses the same syntax.

Warning

Renaming a table may affect views, stored procedures, application code, and scripts that reference the original table name. Update dependent objects after renaming.

⚠️ Best Practices

Best Practice

Use descriptive and consistent table names, perform renaming during planned maintenance when possible, verify application compatibility, test changes in a development environment first, and document schema changes for future maintenance.

🚀 Key Points to Remember

📌 Renaming changes only the table name.
📌 Existing data and table structure remain unchanged.
📌 Syntax differs across SQL database systems.
📌 Dependent objects and applications may need updates.
📌 Renaming improves database readability and maintainability.
📌 Test schema changes before applying them in production.

>>"Clear and meaningful table names make databases easier to understand, maintain, and scale."

Summary

✅ The RENAME TABLE operation allows you to change the name of an existing table without affecting its data or structure. It is an important schema management task that helps maintain clear, consistent, and meaningful database designs as applications evolve.