Topic 4 of 52 · Beginner
ALTER TABLE – RENAME COLUMN
Rename a column without touching its data or type.
01ALTER TABLE – RENAME COLUMN
Module 02 – Database & Table Management (DDL)
02What is it?
RENAME COLUMN changes only the name of a column. The data type, constraints and all stored values stay exactly the same.
03Why do we need it?
Teams adopt naming standards (snake_case, clear names), or a column name turns out misleading — e.g. salary actually stores monthly pay. Renaming fixes the meaning while keeping the data.
04Syntax
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;RENAME COLUMN: MySQL 8.0+, PostgreSQL, Snowflake, BigQuery all support it.
05Parameter explanation
- old_name
- Current column name; must exist.
- new_name
- New column name; must not already exist in the table.
06Sample database
Every example and exercise below starts from a fresh copy of this data.
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
dept_id INT,
salary DECIMAL(10,2),
hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
(1,'Asha',10,85000,'2021-04-12'),
(2,'Ravi',20,52000,'2022-01-03'),
(3,'Meena',10,97000,'2019-09-20'),
(4,'Karan',30,46000,'2023-06-15'),
(5,'Divya',20,61000,'2020-11-30');07Basic coding example
08Step-by-step execution
- 1.MySQL checks salary exists and annual_salary does not.
- 2.Only the metadata (column name) is updated — no data is rewritten.
- 3.Queries must now use annual_salary; SELECT salary will fail.
09Before and after output
Generated live by running the basic example against the sample database.
Running on the SQL engine…
10Intermediate coding example
11Real-world business scenario
When a source system renames a field (cust_nm → customer_name), the data engineer renames the matching staging column, then updates every downstream view and query that referenced the old name.
12Common mistakes
ALTER TABLE employees RENAME salary TO pay;
Without COLUMN, MySQL reads this as renaming the table.
ALTER TABLE employees RENAME COLUMN salary TO pay;
ALTER TABLE employees RENAME COLUMN salery TO pay;
Typo: column salery doesn't exist.
ALTER TABLE employees RENAME COLUMN salary TO pay;
ALTER TABLE employees RENAME COLUMN salary TO name;
name already exists → duplicate column.
Choose a unique new name, e.g. base_salary.
13Important comparisons
| Command (MySQL) | Renames? | Changes type? |
|---|---|---|
| RENAME COLUMN a TO b | Yes | No |
| CHANGE COLUMN a b type | Yes | Yes (must restate type) |
| MODIFY COLUMN a type | No | Yes |
14Practical exercises
Beginner1. Rename dept_name to name in departments.
Reveal solution (15)
Allowed because departments has no other column called name.
Beginner2. Rename hire_date to start_date.
Reveal solution (15)
Data is unchanged.
Beginner3. Rename emp_id to employee_id and show the first two rows.
Reveal solution (15)
Primary key status carries over.
Intermediate4. Rename salary to monthly_salary and show it divided by 12 as monthly_pay.
Reveal solution (15)
Aliases (AS) rename only in the output; RENAME COLUMN renames permanently.
Intermediate5. Rename two columns in employees: name → emp_name, dept_id → department_id.
Reveal solution (15)
MySQL also allows both in one statement separated by commas.
Intermediate6. Show that the old name fails after renaming salary to pay.
Reveal solution (15)
Expected error: no such column salary.
Advanced7. After renaming dept_id to department_id, join employees to departments.
Reveal solution (15)
Every query that used the old name must be updated.
Advanced8. Rename name to full_name, then build a view on it.
Reveal solution (15)
Rename before creating dependent views to avoid breaking them.
16Key takeaways
- RENAME COLUMN changes only the name.
- Available in MySQL 8.0+; older MySQL used CHANGE COLUMN.
- Update dependent views and queries after renaming.
17Knowledge check
1. Which MySQL version introduced RENAME COLUMN?
2. Does RENAME COLUMN change stored values?
3. ALTER TABLE t RENAME x TO y (without COLUMN) in MySQL means…
Interview questions
RENAME COLUMN vs CHANGE COLUMN?
RENAME COLUMN only renames. CHANGE COLUMN renames and requires you to restate the full definition, so it can also change the type.
Lesson test
Take an 8-question test on “ALTER TABLE – RENAME COLUMN” — concepts, output prediction, bug spotting and interview questions.