Renaming Columns and Tables

Both operations are metadata changes, and both are fast on every modern engine — no rows are rewritten, because the data does not move. A renamed column keeps its data and its position; a renamed table keeps its rows, its indexes and its constraints.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(200) NOT NULL
);

INSERT INTO customers (id, full_name, email) VALUES (1, 'Ada Lovelace', '[email protected]');

-- Rename a column.
ALTER TABLE customers RENAME COLUMN full_name TO name;

-- Rename the table.
ALTER TABLE customers RENAME TO clients;

SELECT id, name, email FROM clients;

One row comes back: id 1, name 'Ada Lovelace', email [email protected]. Nothing was copied and nothing was lost.

What does change is everything that referred to the old name. In SQLite, renaming a table automatically updates the views and triggers that referenced it, and it rewrites the REFERENCES clauses of foreign keys pointing at it. Renaming a column does the same for views and triggers that use it. In PostgreSQL, the dependency tracking is more sophisticated: a rename does not break dependent views, because the view stores its dependency on the column's identity rather than its name. MySQL is the strictest of the three and will refuse to rename a table that a view depends on unless you drop the view first.

The naming rules are worth knowing. RENAME COLUMN and RENAME TO are the SQL standard forms and are supported by SQLite from 3.25, PostgreSQL, MySQL and SQL Server. Older MySQL syntax used CHANGE COLUMN, which required repeating the whole column definition — a spelling quirk worth remembering if you see it in an old script. SQLite also supports the older RENAME TO for tables, which is the same as RENAME TO.

A rename in SQLite is a single statement, and the data survives it untouched. Here is the same rename done twice — once on the column and once on the table — with a check that the rows are still intact.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(200) NOT NULL
);

INSERT INTO customers (id, full_name, email) VALUES
  (1, 'Ada Lovelace', '[email protected]'),
  (2, 'Grace Hopper', '[email protected]');

-- Rename the column.
ALTER TABLE customers RENAME COLUMN full_name TO name;

-- Rename the table.
ALTER TABLE customers RENAME TO clients;

SELECT id, name, email FROM clients ORDER BY id;

Two rows come back, with the column now called name and the table now called clients. Both rows are intact, with their original values.

The order of the two renames does not matter, and neither does the fact that the second rename was issued against the renamed table — the statement applies to the current name. What matters is that anything referencing the old name has to be updated, and that includes application code, views, triggers, reporting queries and any other system that talks to the database.

One thing worth checking after a rename is that the indexes and constraints followed the column. SQLite keeps the index on the renamed column under the new name, and a UNIQUE constraint on the renamed column stays unique. You can confirm by listing the schema: SELECT sql FROM sqlite_master WHERE name = 'clients' shows the current definition, and the indexes are visible by selecting from sqlite_master where type is 'index'.

The database change itself is trivial. The risk is entirely in everything that is still using the old name at the moment you change it, and a rename cannot be made backwards compatible — there is no way for one column to have two names simultaneously.

That is why a rename has to be a coordinated, multi-deploy operation rather than a migration. The sequence is: first add the new column alongside the old one, keeping the old column in place and still being written. Then deploy the code that writes to both, or writes only to the new one while still reading the old. Then backfill the old column from the new one so the two agree. Then deploy the code that reads only the new column. Only once no deployed code references the old name do you drop the old column.

Each of those steps is a normal deploy, and any of them can be rolled back independently. The rename itself never happens, because you never rename — you add, dual-write, backfill, switch the read, and drop. That is more work than a single ALTER, and it is the correct amount of work for a live system.

Two other risks. A rename inside a transaction that also does other work can hold locks for longer than expected on some engines, though the metadata change itself is quick. And if any part of the system builds SQL by string concatenation against the column name, it will break in a way that no test catches unless the test uses the same string. Grep for the old name across the whole codebase — including the reporting layer, the data warehouse jobs, the BI tool's saved queries and anything that reads the database directly — before you start.

The exception is a greenfield table that nothing reads yet. There, a plain rename is completely safe and there is no reason to go through the sequence.