ALTER TABLE in SQL: ADD, DROP, MODIFY, and RENAME Column or Table

Schemas are rarely perfect forever — new features need new columns, old columns become obsolete, and data types occasionally need to change. ALTER TABLE is the statement that lets a table evolve after it's already created and populated with data, without having to drop and rebuild it from scratch. This lesson covers ADD, DROP, MODIFY, and RENAME operations for both columns and tables.

What Is ALTER TABLE?

ALTER TABLE is the DDL statement used to change the structure of an existing table — adding new columns, removing old ones, changing a column's data type or constraints, or renaming columns and the table itself. Unlike CREATE TABLE, ALTER TABLE operates on a table that may already contain live data, so its effects need to be considered carefully.

What You'll Learn

• Add new columns to an existing table using ALTER TABLE ADD.
• Remove columns safely using ALTER TABLE DROP COLUMN.
• Change a column's data type or constraints using MODIFY.
• Rename columns and tables using RENAME COLUMN and RENAME TABLE-style syntax.

Key Terms to Know

• ALTER TABLE: The DDL statement used to change an existing table's structure.
• ADD COLUMN: Adds a new column to an existing table.
• DROP COLUMN: Permanently removes a column and all its data from a table.
• MODIFY COLUMN: Changes an existing column's data type, size, or constraints.
• CHANGE COLUMN: A MySQL-specific clause that can rename a column while also changing its definition.

Adding and Dropping Columns

ALTER TABLE table_name ADD COLUMN column_name data_type; appends a new column to an existing table. In MySQL, you can also control its position with AFTER existing_column or FIRST, since by default new columns are added at the end of the table.

ALTER TABLE table_name DROP COLUMN column_name; permanently deletes that column and every value stored in it for every row — this action cannot be undone, so it's good practice to back up the table or export the column's data before dropping it in production.

Modifying Column Data Types and Constraints

ALTER TABLE table_name MODIFY COLUMN column_name new_data_type [constraints]; changes an existing column's definition, such as growing a VARCHAR(50) column to VARCHAR(100), or adding a NOT NULL constraint to a column that previously allowed NULL.

MODIFY can fail if existing data is incompatible with the new definition — for example, trying to shrink a VARCHAR(100) column to VARCHAR(10) when some stored values exceed 10 characters will be rejected (or truncated with a warning, depending on SQL mode). MySQL's CHANGE COLUMN clause works similarly but additionally allows renaming the column at the same time.

Renaming Columns and Tables

In modern MySQL (8.0+), ALTER TABLE table_name RENAME COLUMN old_name TO new_name; renames a column without touching its data type. Older syntax uses CHANGE COLUMN old_name new_name data_type to achieve the same result while also requiring you to restate the full column definition.

Renaming a whole table can be done with ALTER TABLE old_table_name RENAME TO new_table_name; or the dedicated RENAME TABLE statement (covered in its own lesson), which is useful when an entity's name in the business changes but the underlying data structure doesn't.

Visual Summary

Imagine a table as a spreadsheet that's already in use, with rows of real data filled in. ALTER TABLE ADD inserts a brand-new blank column at the end (or wherever specified). DROP COLUMN deletes an entire column along with everything written in it. MODIFY COLUMN keeps the column in place but changes the rules for what can be written in its cells going forward. RENAME just relabels the column header without touching any cell contents.

ALTER TABLE Operations at a Glance

Operation	Syntax Pattern	Effect
Add column	ADD COLUMN col_name TYPE	Adds a new column, default NULL unless specified
Drop column	DROP COLUMN col_name	Permanently deletes the column and its data
Modify column	MODIFY COLUMN col_name NEW_TYPE	Changes data type or constraints in place
Rename column	RENAME COLUMN old TO new	Renames without altering stored data
Rename table	RENAME TO new_table_name	Renames the whole table

SQL Example

-- Starting table
CREATE TABLE employees (
  employee_id INT PRIMARY KEY AUTO_INCREMENT,
  full_name   VARCHAR(100) NOT NULL,
  salary      DECIMAL(10, 2) NOT NULL
);

-- Add a new column for department, positioned after full_name
ALTER TABLE employees
  ADD COLUMN department VARCHAR(50) DEFAULT 'Unassigned' AFTER full_name;

-- Modify salary's precision and add a CHECK-friendly NOT NULL
ALTER TABLE employees
  MODIFY COLUMN salary DECIMAL(12, 2) NOT NULL;

-- Rename a column from full_name to employee_name
ALTER TABLE employees
  RENAME COLUMN full_name TO employee_name;

-- Drop a column that is no longer needed
ALTER TABLE employees
  DROP COLUMN department;

Each ALTER TABLE statement targets a single, focused structural change: first a column is added, then an existing column's precision is widened, then a column is renamed, and finally a column is removed entirely. Running these as separate, deliberate statements (rather than one giant change) makes it easier to track exactly what changed and roll back a single step if something goes wrong.

Real-World Examples

• SaaS platforms use ALTER TABLE ADD COLUMN to introduce new optional fields, like a feature flag or subscription tier, without downtime.
• Data migrations from legacy systems often use MODIFY COLUMN to widen VARCHAR limits or upgrade INT to BIGINT as data volume grows.
• Companies rebranding a feature internally use RENAME COLUMN to align database naming with new product terminology.
• Cleanup migrations use DROP COLUMN to remove fields made obsolete by a feature that was deprecated or replaced.

Best Practices and Pro Tips

• Always take a backup or at least export the affected column's data before running DROP COLUMN in production — there is no built-in undo.
• On very large tables, ALTER TABLE operations can lock the table or take a long time; test the migration on a copy of production-sized data first, and consider tools like online schema change utilities for zero-downtime alters.
• Make ALTER TABLE changes incremental and one-purpose-at-a-time in version-controlled migration files, rather than one large unreviewable change.

Common Mistakes to Avoid

• Running DROP COLUMN without a backup, permanently losing data that was actually still needed.
• Trying to MODIFY a column to a smaller size than some existing values, causing silent truncation or outright errors depending on SQL mode.
• Forgetting that MODIFY COLUMN requires restating the entire column definition, including constraints that should be preserved — omitting NOT NULL during a MODIFY can accidentally remove it.
• Running large ALTER TABLE operations directly on a busy production table during peak hours, causing locking and downtime.

Interview Questions

Q1. What is the difference between DROP COLUMN and MODIFY COLUMN?

DROP COLUMN permanently removes a column and all of its data from the table. MODIFY COLUMN keeps the column but changes its data type, size, or constraints, without deleting the column itself.

Q2. How would you rename a column without changing its data type in MySQL 8?

Use ALTER TABLE table_name RENAME COLUMN old_name TO new_name;, which is available in MySQL 8.0 and later and changes only the name, leaving the data type and existing data untouched.

Q3. What can go wrong when using MODIFY COLUMN to shrink a column's size?

If existing data exceeds the new size limit, MySQL will either reject the operation or truncate the data with a warning, depending on the active SQL mode. It's important to check existing data lengths before shrinking a column.

Q4. Why might ALTER TABLE be risky on a large production table?

Many ALTER TABLE operations require rebuilding the table or rewriting large portions of it, which can lock the table for reads and writes during the operation, potentially causing downtime on busy systems.

Practice MCQs

1. Which clause adds a brand-new column to an existing table?

1. MODIFY COLUMN
2. ADD COLUMN
3. RENAME COLUMN
4. DROP COLUMN

Answer: B. ADD COLUMN

Explanation: ADD COLUMN is the correct ALTER TABLE clause for introducing a new column to an existing table.

2. Which clause permanently deletes a column and its data?

1. DROP COLUMN
2. MODIFY COLUMN
3. RENAME COLUMN
4. ADD COLUMN

Answer: A. DROP COLUMN

Explanation: DROP COLUMN removes the specified column entirely, along with all data stored in it for every row.

3. In MySQL 8.0+, which statement correctly renames a column without altering its type?

1. ALTER TABLE t MODIFY old TO new;
2. ALTER TABLE t RENAME COLUMN old TO new;
3. ALTER TABLE t DROP old ADD new;
4. RENAME COLUMN t.old TO t.new;

Answer: B. ALTER TABLE t RENAME COLUMN old TO new;

Explanation: RENAME COLUMN, available from MySQL 8.0, renames a column while preserving its existing data type and data.

Quick Revision Points

• ADD COLUMN introduces a new column; DROP COLUMN permanently removes one along with its data.
• MODIFY COLUMN changes data type/constraints in place; it can fail if existing data is incompatible.
• RENAME COLUMN (MySQL 8.0+) renames without altering data type; CHANGE COLUMN can rename and redefine simultaneously.
• Large ALTER TABLE operations can lock tables, so they should be tested and scheduled carefully in production.

Conclusion

• ALTER TABLE lets a schema evolve safely as requirements change, without rebuilding tables from scratch.
• Every structural change should be deliberate, tested, and ideally backed up before running on production data.
• Understanding ADD, DROP, MODIFY, and RENAME covers the vast majority of real-world schema evolution needs.

ALTER TABLE is how a schema adapts over time without losing existing data: ADD COLUMN introduces new fields, DROP COLUMN removes obsolete ones, MODIFY COLUMN updates data types and constraints, and RENAME COLUMN relabels fields cleanly. Used carefully — with backups and incremental changes — ALTER TABLE keeps a production database aligned with evolving application requirements. The next lesson compares DROP TABLE, TRUNCATE TABLE, and DELETE, three very different ways data can be removed from a database.