← SQL roadmap

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

Basic example · runs on the real engine

08Step-by-step execution

  1. 1.MySQL checks that employees.name exists.
  2. 2.It builds the new definition VARCHAR(120) NOT NULL and verifies every existing value fits (no NULL names, none longer than 120).
  3. 3.The table is rewritten with the new definition; all five rows are copied across unchanged.
  4. 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

Intermediate 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

ClauseChange type?Rename?Notes
MODIFY COLUMNYesNoMost common for widening/tightening
CHANGE COLUMNYesYesMust list old and new name
RENAME COLUMNNoYesMySQL 8.0+, name only
ALTER COLUMN ... SET DEFAULTNoNoOnly the default value

14Practical exercises

Beginner1. Widen departments.dept_name to VARCHAR(100) NOT NULL.

Reveal solution (15)
Solution · run it

Repeat NOT NULL so the rule survives.

Beginner2. Make employees.salary a DECIMAL(12,2).

Reveal solution (15)
Solution · run it

COLUMN is optional; the data is kept.

Beginner3. Rename hire_date to joined_on keeping type DATE, using CHANGE.

Reveal solution (15)
Solution · run it

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)
Solution · run it

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)
Solution · run it

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)
Solution · run it

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)
Solution · run it

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)
Solution · run it

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.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “ALTER TABLE – MODIFY & CHANGE COLUMN” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.