Modifying Tables with ALTER TABLE0%

Modifying Tables with ALTER TABLE

Beginner12 min read•Updated: 2026-09-12

Modifying Tables with ALTER TABLE: Add, Drop, Modify, and Rename

In real-world software engineering, application requirements evolve constantly. You will frequently need to add a new column to store user profile pictures, expand a character length from 50 to 100, rename a confusing column, or drop a deprecated field. All schema changes on existing tables are performed using ALTER TABLE.

1. Adding New Columns (`ADD COLUMN`)

To add a new column to an existing table:

1-- Add column at the end of the table:
2ALTER TABLE customers
3ADD COLUMN middle_name VARCHAR(50);
4 
5-- Add column at the very beginning of the table:
6ALTER TABLE customers
7ADD COLUMN national_id VARCHAR(20) FIRST;
8 
9-- Add column immediately after a specific existing column:
10ALTER TABLE customers
11ADD COLUMN date_of_birth DATE AFTER last_name;

2. Removing Columns (`DROP COLUMN`)

To delete a column and all its stored data:

1ALTER TABLE customers
2DROP COLUMN middle_name;

Important Warning

[!CAUTION] DROP COLUMN physically removes the data across every row in the table! This action is irreversible. Always verify that no running application code references the column before dropping it.

3. `MODIFY` vs `CHANGE`: Modifying Existing Columns

When changing column attributes, MySQL provides two distinct commands:

`MODIFY COLUMN` (Change Data Type or Constraints, Keep Name)

Use MODIFY when you want to alter a column's data type, length, or nullability without changing its name:

1-- Expand email column from VARCHAR(100) to VARCHAR(255):
2ALTER TABLE customers
3MODIFY COLUMN email VARCHAR(255) NOT NULL;

`CHANGE COLUMN` (Rename Column AND Optionally Change Data Type)

Use CHANGE when you want to rename a column:

1-- Syntax: ALTER TABLE tbl CHANGE old_name new_name data_type [constraints];
2ALTER TABLE customers
3CHANGE COLUMN phone_number contact_phone VARCHAR(25) NOT NULL;

Note

[!NOTE] In MySQL 8.0+, you can also use the cleaner syntax: ALTER TABLE customers RENAME COLUMN phone_number TO contact_phone;

4. Renaming Tables

You can rename a table using either ALTER TABLE or the standalone RENAME TABLE command:

1-- Method 1: Using ALTER TABLE
2ALTER TABLE customers RENAME TO client_accounts;
3 
4-- Method 2: Using RENAME TABLE (Supports atomic renaming of multiple tables!)
5RENAME TABLE 
6    old_orders TO orders_archive_2025,
7    new_orders TO orders;

5. Adding & Dropping Constraints

1-- Add a unique constraint to an existing table:
2ALTER TABLE customers
3ADD CONSTRAINT uq_customer_phone UNIQUE (contact_phone);
4 
5-- Drop a foreign key constraint:
6ALTER TABLE orders
7DROP FOREIGN KEY fk_orders_customer;
8 
9-- Drop an index:
10ALTER TABLE customers
11DROP INDEX uq_customer_phone;

6. Best Practices & Common Pitfalls

Online DDL & Table Locking: On large tables (millions of rows), running ALTER TABLE can lock writes or rebuild the entire table on disk, causing application timeouts. Modern MySQL supports Online DDL (ALGORITHM = INPLACE, LOCK = NONE), allowing reads and writes to continue while modifications execute.
Shrinking Column Lengths: Be cautious when reducing a column's width (e.g., from VARCHAR(255) to VARCHAR(50)). If any existing rows contain strings longer than 50 characters, MySQL in strict mode will abort with an error!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which clause adds a new column immediately following an existing column named last_name?

Question 2

What is the key difference between MODIFY COLUMN and CHANGE COLUMN in MySQL?

Question 3

Which modern MySQL 8.0+ statement renames an existing column phone to mobile_phone cleanly?

Question 4

What occurs if you attempt to alter a VARCHAR(100) column to VARCHAR(20) when existing rows contain 40-character strings in strict SQL mode?

Question 5

Why is the standalone RENAME TABLE statement favored when performing zero-downtime database deployments?

Next Lesson

Truncating vs Dropping Tables

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Inspecting Tables with DESCRIBE & SHOW CREATE	Truncating vs Dropping Tables

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Truncating vs Dropping Tables

Modifying Tables with ALTER TABLE

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Modifying Tables with ALTER TABLE: Add, Drop, Modify, and Rename

1. Adding New Columns (`ADD COLUMN`)

To add a new column to an existing table:

1-- Add column at the end of the table:
2ALTER TABLE customers
3ADD COLUMN middle_name VARCHAR(50);
4 
5-- Add column at the very beginning of the table:
6ALTER TABLE customers
7ADD COLUMN national_id VARCHAR(20) FIRST;
8 
9-- Add column immediately after a specific existing column:
10ALTER TABLE customers
11ADD COLUMN date_of_birth DATE AFTER last_name;

2. Removing Columns (`DROP COLUMN`)

To delete a column and all its stored data:

1ALTER TABLE customers
2DROP COLUMN middle_name;

Important Warning

3. `MODIFY` vs `CHANGE`: Modifying Existing Columns

When changing column attributes, MySQL provides two distinct commands:

`MODIFY COLUMN` (Change Data Type or Constraints, Keep Name)

Use MODIFY when you want to alter a column's data type, length, or nullability without changing its name:

1-- Expand email column from VARCHAR(100) to VARCHAR(255):
2ALTER TABLE customers
3MODIFY COLUMN email VARCHAR(255) NOT NULL;

`CHANGE COLUMN` (Rename Column AND Optionally Change Data Type)

Use CHANGE when you want to rename a column:

1-- Syntax: ALTER TABLE tbl CHANGE old_name new_name data_type [constraints];
2ALTER TABLE customers
3CHANGE COLUMN phone_number contact_phone VARCHAR(25) NOT NULL;

Note

[!NOTE] In MySQL 8.0+, you can also use the cleaner syntax: ALTER TABLE customers RENAME COLUMN phone_number TO contact_phone;

4. Renaming Tables

You can rename a table using either ALTER TABLE or the standalone RENAME TABLE command:

1-- Method 1: Using ALTER TABLE
2ALTER TABLE customers RENAME TO client_accounts;
3 
4-- Method 2: Using RENAME TABLE (Supports atomic renaming of multiple tables!)
5RENAME TABLE 
6    old_orders TO orders_archive_2025,
7    new_orders TO orders;

5. Adding & Dropping Constraints

1-- Add a unique constraint to an existing table:
2ALTER TABLE customers
3ADD CONSTRAINT uq_customer_phone UNIQUE (contact_phone);
4 
5-- Drop a foreign key constraint:
6ALTER TABLE orders
7DROP FOREIGN KEY fk_orders_customer;
8 
9-- Drop an index:
10ALTER TABLE customers
11DROP INDEX uq_customer_phone;

6. Best Practices & Common Pitfalls

Online DDL & Table Locking: On large tables (millions of rows), running ALTER TABLE can lock writes or rebuild the entire table on disk, causing application timeouts. Modern MySQL supports Online DDL (ALGORITHM = INPLACE, LOCK = NONE), allowing reads and writes to continue while modifications execute.
Shrinking Column Lengths: Be cautious when reducing a column's width (e.g., from VARCHAR(255) to VARCHAR(50)). If any existing rows contain strings longer than 50 characters, MySQL in strict mode will abort with an error!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which clause adds a new column immediately following an existing column named last_name?

Question 2

What is the key difference between MODIFY COLUMN and CHANGE COLUMN in MySQL?

Question 3

Which modern MySQL 8.0+ statement renames an existing column phone to mobile_phone cleanly?

Question 4

What occurs if you attempt to alter a VARCHAR(100) column to VARCHAR(20) when existing rows contain 40-character strings in strict SQL mode?

Question 5

Why is the standalone RENAME TABLE statement favored when performing zero-downtime database deployments?

Next Lesson

Truncating vs Dropping Tables

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Inspecting Tables with DESCRIBE & SHOW CREATE	Truncating vs Dropping Tables

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Modifying Tables with ALTER TABLE: Add, Drop, Modify, and Rename

1. Adding New Columns (ADD COLUMN)

2. Removing Columns (DROP COLUMN)

3. MODIFY vs CHANGE: Modifying Existing Columns

MODIFY COLUMN (Change Data Type or Constraints, Keep Name)

CHANGE COLUMN (Rename Column AND Optionally Change Data Type)

4. Renaming Tables

5. Adding & Dropping Constraints

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Truncating vs Dropping Tables

Related Lessons

Practice Quiz

Loading Page Content...

Modifying Tables with ALTER TABLE: Add, Drop, Modify, and Rename

1. Adding New Columns (ADD COLUMN)

2. Removing Columns (DROP COLUMN)

3. MODIFY vs CHANGE: Modifying Existing Columns

MODIFY COLUMN (Change Data Type or Constraints, Keep Name)

CHANGE COLUMN (Rename Column AND Optionally Change Data Type)

4. Renaming Tables

5. Adding & Dropping Constraints

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Truncating vs Dropping Tables

Related Lessons

Practice Quiz

1. Adding New Columns (`ADD COLUMN`)

2. Removing Columns (`DROP COLUMN`)

3. `MODIFY` vs `CHANGE`: Modifying Existing Columns

`MODIFY COLUMN` (Change Data Type or Constraints, Keep Name)

`CHANGE COLUMN` (Rename Column AND Optionally Change Data Type)

1. Adding New Columns (`ADD COLUMN`)

2. Removing Columns (`DROP COLUMN`)

3. `MODIFY` vs `CHANGE`: Modifying Existing Columns

`MODIFY COLUMN` (Change Data Type or Constraints, Keep Name)

`CHANGE COLUMN` (Rename Column AND Optionally Change Data Type)