Topic 11 of 52 · Intermediate
ALTER TABLE – MODIFY & CHANGE COLUMN
Change a column's data type, size, NULL rule or default — and optionally its name.
01ALTER TABLE – MODIFY & CHANGE COLUMN
Module 02 – Database & Table Management (DDL)
02What is it?
MODIFY COLUMN redefines an existing column: new data type, new length, NOT NULL / NULL, or a new DEFAULT. The column keeps its name.
CHANGE COLUMN does everything MODIFY does and can also rename the column in the same statement, so you must write the old name and the new name (even if they are identical).
03Why do we need it?
Real tables outgrow their first design: a VARCHAR(20) email column starts truncating, a salary stored as INT needs paise, or a column that was optional must become mandatory.
MODIFY/CHANGE fix the definition in place while keeping every existing row — no need to drop and reload the table.
04Syntax
ALTER TABLE table_name
MODIFY [COLUMN] column_name new_datatype [NULL | NOT NULL] [DEFAULT value] [FIRST | AFTER col];
ALTER TABLE table_name
CHANGE [COLUMN] old_name new_name new_datatype [constraints] [FIRST | AFTER col];MODIFY and CHANGE are MySQL/MariaDB syntax. PostgreSQL uses ALTER COLUMN col TYPE ..., SQL Server uses ALTER COLUMN col type. In this browser sandbox the table is rebuilt to apply the change.
05Parameter explanation
- MODIFY
- Redefines a column; the name stays the same.
- CHANGE old_name new_name
- Redefines and (optionally) renames. Repeat the name if you only want to change the type.
- new_datatype
- The full new type, e.g. VARCHAR(120) or DECIMAL(12,2). It replaces the old definition completely.
- NULL / NOT NULL / DEFAULT
- Any rule you don't repeat is lost — MODIFY rewrites the entire column definition.
- FIRST / AFTER col
- Optional MySQL clause to move the column's position.
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 that employees.name exists.
- 2.It builds the new definition VARCHAR(120) NOT NULL and verifies every existing value fits (no NULL names, none longer than 120).
- 3.The table is rewritten with the new definition; all five rows are copied across unchanged.
- 4.DESCRIBE now shows name as varchar(120) / NOT NULL.
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
An upstream API starts sending 15-digit phone numbers, so the data engineer widens stg_customers.phone from VARCHAR(10) to VARCHAR(15) with MODIFY before the next load fails.
During a naming clean-up, CHANGE renames cust_nm to customer_name and widens it in one deployment step, keeping migration scripts short.
12Common mistakes
ALTER TABLE employees MODIFY salary DECIMAL(12,2);
If salary was NOT NULL DEFAULT 0, those rules are silently dropped — MODIFY replaces the whole definition.
ALTER TABLE employees MODIFY salary DECIMAL(12,2) NOT NULL DEFAULT 0;
ALTER TABLE employees CHANGE salary DECIMAL(12,2);
CHANGE needs both the old and the new name; MySQL reads DECIMAL as the new name and fails.
ALTER TABLE employees CHANGE salary salary DECIMAL(12,2);
ALTER TABLE employees MODIFY dept_id INT NOT NULL; -- while some rows have NULL dept_id
Existing NULLs violate the new rule, so the statement fails (strict mode).
UPDATE employees SET dept_id = 0 WHERE dept_id IS NULL; then run the MODIFY.
13Important comparisons
| Clause | Change type? | Rename? | Notes |
|---|---|---|---|
| MODIFY COLUMN | Yes | No | Most common for widening/tightening |
| CHANGE COLUMN | Yes | Yes | Must list old and new name |
| RENAME COLUMN | No | Yes | MySQL 8.0+, name only |
| ALTER COLUMN ... SET DEFAULT | No | No | Only the default value |
14Practical exercises
Beginner1. Widen departments.dept_name to VARCHAR(100) NOT NULL.
Reveal solution (15)
Repeat NOT NULL so the rule survives.
Beginner2. Make employees.salary a DECIMAL(12,2).
Reveal solution (15)
COLUMN is optional; the data is kept.
Beginner3. Rename hire_date to joined_on keeping type DATE, using CHANGE.
Reveal solution (15)
CHANGE = rename + redefine.
Intermediate4. Give employees.salary a default of 30000 and NOT NULL using MODIFY, then insert an employee without salary.
Reveal solution (15)
New rows without salary get the default.
Intermediate5. Rename name to full_name and widen it to VARCHAR(150) NOT NULL in a single statement.
Reveal solution (15)
Only CHANGE can do both at once.
Intermediate6. Try to make dept_id NOT NULL after inserting an employee with no department. What happens? Fix it.
Reveal solution (15)
Clean the NULLs first, then tighten the rule.
Advanced7. Convert salary to monthly_pay DECIMAL(10,2) NOT NULL and store monthly amounts (annual / 12).
Reveal solution (15)
Structure change first, then a data fix-up UPDATE.
Advanced8. Write a re-runnable migration that renames dept_name to name and widens it to VARCHAR(80) NOT NULL.
Reveal solution (15)
In production, check information_schema.COLUMNS first so the script can run twice safely.
16Key takeaways
- MODIFY rewrites the full column definition — repeat NOT NULL/DEFAULT you want to keep.
- CHANGE can rename and redefine; always write old and new names.
- Tightening rules (NOT NULL, shorter length) fails if existing rows break them.
- On big tables MODIFY/CHANGE can rebuild the table — schedule them carefully.
17Knowledge check
1. Which clause can rename AND change the type of a column?
2. salary is INT NOT NULL. After ALTER TABLE t MODIFY salary BIGINT; is it still NOT NULL?
3. MODIFY dept_id INT NOT NULL fails. The likely cause?
Interview questions
MODIFY vs CHANGE in MySQL?
Both redefine a column. CHANGE also renames, so its syntax needs old_name new_name definition; MODIFY keeps the name and only needs name definition.
How do you change a column type on a very large production table?
Test on a copy, check for values that won't fit, use online DDL (ALGORITHM=INPLACE/INSTANT where possible) or tools like gh-ost/pt-online-schema-change, and run it in a low-traffic window.
Lesson test
Take an 8-question test on “ALTER TABLE – MODIFY & CHANGE COLUMN” — concepts, output prediction, bug spotting and interview questions.