RENAME

Definition

Renaming lets you change the name of a table, column, or other object without recreating it — data, indexes, and most permissions are preserved; only the name changes.

sql
-- PostgreSQL
ALTER TABLE employees RENAME TO staff;
ALTER TABLE staff RENAME COLUMN salary TO base_salary;

-- MySQL
RENAME TABLE employees TO staff;
ALTER TABLE staff RENAME COLUMN salary TO base_salary;   -- MySQL 8.0+
ALTER TABLE staff CHANGE salary base_salary DECIMAL(10,2); -- older MySQL syntax (also allows type change)

-- SQL Server
EXEC sp_rename 'employees', 'staff';
EXEC sp_rename 'staff.salary', 'base_salary', 'COLUMN';

How It Works

The engine updates the catalog entry that maps the object's name to its underlying storage/identifier — the physical data files and internal object ID typically don't change, only the label used to address it. This is why renames are usually near-instant, unlike a full rebuild.

Worked example — a phased table rename during migration:

sql
ALTER TABLE employees RENAME TO staff;
CREATE VIEW employees AS SELECT * FROM staff;   -- compatibility shim
-- Update application code over the next release cycle to use `staff` directly.
-- Once nothing queries the `employees` view anymore, drop it.
DROP VIEW employees;

This lets a rename roll out without a hard cutover, avoiding breaking every caller simultaneously.

Edge Cases and Pitfalls

• Dependent objects may still reference the old name internally: in some engines (notably older MySQL), a view defined with SELECT * FROM employees can break or behave unexpectedly if the underlying table is renamed, because the view's definition was resolved/cached at creation time.
• Foreign key constraints generally continue to work after a table rename (they track the object internally, not just the name) — but always verify on your specific engine and version.
• Application/connection code and cached query plans may reference the old name; a rename doesn't retroactively fix hardcoded SQL strings scattered through an application.
• Case sensitivity: renaming to a name differing only by case can behave inconsistently across operating systems/engines (e.g., case-insensitive collations).
• Locking during rename: even though it's metadata-only, most engines still take a brief exclusive lock, which can momentarily block concurrent queries on a busy table — rename during low-traffic windows for critical tables.
• Renaming a column that's referenced in stored procedures, triggers, or ORM model definitions is a very common source of runtime errors that only surface later, not at rename time.

Key Takeaways / Q&A

Q: Does renaming a table change its data or indexes? A: No — data and indexes are preserved; only the name (and possibly dependent object bookkeeping) changes.

Q: Why is a compatibility view a good idea when renaming a heavily used table? A: It lets old queries keep working under the old name during a transition period while new code and be migrated to the new name gradually, avoiding a hard, all-at-once cutover.

Q: Is a rename always instantaneous? A: It's typically fast (metadata-only) but still requires a brief lock, and dependent-object bookkeeping (especially in older MySQL versions with views) can complicate things beyond a pure name change.