Altering Tables

Introduction

Tables rarely stay exactly the way you first created them. As a project grows, you often need to add a new column, fix a column that was made too small, rename something to be clearer, or remove a column you no longer need.

MySQL gives you the ALTER TABLE statement for exactly this purpose. It lets you change the structure of an existing table without having to drop it and recreate it, which means the data already stored in the table is preserved.

The ALTER TABLE Statement

ALTER TABLE is the general-purpose statement for changing a table's structure. It is always followed by the name of the table you want to change, and then one of several clauses describing exactly what change to make.

ALTER TABLE table_name
clause_describing_the_change;

Adding a Column

Use ADD COLUMN when a table is missing a piece of information you now need to track. You specify the new column's name and its data type, just like when creating a table.

ALTER TABLE students
ADD COLUMN email VARCHAR(100);

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

For every row that already existed in the table, the new email column will be filled in with NULL, since MySQL has no way to know what each student's email should be.

Modifying a Column

Use MODIFY COLUMN when the column already exists but its data type or size needs to change. A very common example is a VARCHAR column that was created too small and now needs to hold longer values.

ALTER TABLE students
MODIFY COLUMN email VARCHAR(150);

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

The column keeps its name (email) but can now store up to 150 characters instead of 100. MODIFY COLUMN can also change the data type entirely, for example from INT to BIGINT, as long as the existing data can be safely converted.

Renaming a Column

Use RENAME COLUMN when the column's name itself needs to change but its data type stays the same. This is useful for fixing a typo or making a name clearer.

ALTER TABLE students
RENAME COLUMN email TO contact_email;

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

Every value already stored in that column is preserved — only the column's name changes, from email to contact_email.

Dropping a Column

Use DROP COLUMN when a column is no longer needed. This permanently removes both the column and every value stored in it for every row — so use it carefully.

ALTER TABLE students
DROP COLUMN contact_email;

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

Worked Example: Evolving the students Table

Let's walk through a realistic sequence of changes to the students table, starting from its original structure and ending with an email column added correctly.

-- Original table
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(2)
);

-- Step 1: Add an email column
ALTER TABLE students
ADD COLUMN email VARCHAR(100);

-- Step 2: We realize some emails are longer than expected, so widen it
ALTER TABLE students
MODIFY COLUMN email VARCHAR(150);

-- Step 3: Rename it to be more descriptive
ALTER TABLE students
RENAME COLUMN email TO student_email;

-- Check the final structure
DESCRIBE students;

+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| id            | int          | NO   | PRI | NULL    |       |
| name          | varchar(50)  | YES  |     | NULL    |       |
| age           | int          | YES  |     | NULL    |       |
| grade         | varchar(2)   | YES  |     | NULL    |       |
| student_email | varchar(150) | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

DESCRIBE students shows the full, current structure of the table, which is a great way to double-check the effect of every ALTER TABLE statement you run.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

ALTER TABLE is the tool you reach for whenever a table's structure needs to evolve. Its ADD COLUMN, MODIFY COLUMN, RENAME COLUMN, and DROP COLUMN clauses cover the vast majority of real-world structural changes.

In this lesson, you learned how to add, modify, rename, and remove columns from an existing table, and walked through a realistic example evolving the students table. Next, you will learn how to remove an entire table using DROP TABLE.