MySQL ALTER TABLE

After a table has been created and is in use, you will often need to make changes to its structure — adding new columns, removing columns that are no longer needed, or adjusting a column's data type. The ALTER TABLE statement handles all of these modifications without requiring you to drop and recreate the table.

ADD COLUMN

Use ADD COLUMN to insert a new column into an existing table. By default the new column is added at the end:

ALTER TABLE students
ADD COLUMN phone VARCHAR(20);

To place the new column at a specific position, use AFTER column_name:

ALTER TABLE students
ADD COLUMN phone VARCHAR(20) AFTER email;

To add it as the very first column, use FIRST:

ALTER TABLE students
ADD COLUMN profile_pic VARCHAR(255) FIRST;

Note: When you add a new column to a table that already contains rows, existing rows will have NULL in the new column unless you specify a DEFAULT value.

DROP COLUMN

Remove a column you no longer need with DROP COLUMN:

ALTER TABLE students
DROP COLUMN phone;

Warning: Dropping a column permanently deletes all the data stored in that column for every row in the table. This cannot be undone without a backup.

MODIFY COLUMN

Use MODIFY COLUMN to change a column's data type or constraints while keeping its name the same:

-- Change 'age' from INT to TINYINT UNSIGNED
ALTER TABLE students
MODIFY COLUMN age TINYINT UNSIGNED;

-- Make the 'city' column NOT NULL with a default value
ALTER TABLE students
MODIFY COLUMN city VARCHAR(80) NOT NULL DEFAULT 'Unknown';

Tip: When modifying a column, you must always re-specify the full column definition (data type + all constraints), not just the part you want to change.

RENAME COLUMN

To rename a column without changing its data type, use RENAME COLUMN (available in MySQL 8.0+):

ALTER TABLE students
RENAME COLUMN joined_at TO enrollment_date;

For older MySQL versions (5.x), use CHANGE instead, which requires you to re-specify the column definition:

-- MySQL 5.x syntax: CHANGE old_name new_name definition
ALTER TABLE students
CHANGE joined_at enrollment_date DATE;

RENAME TABLE

You can rename the entire table using RENAME TO:

ALTER TABLE students
RENAME TO enrolled_students;

ADD and DROP Constraints

You can also add or remove constraints on existing columns:

-- Add a UNIQUE constraint to the 'email' column
ALTER TABLE students
ADD CONSTRAINT uq_email UNIQUE (email);

-- Remove that UNIQUE constraint
ALTER TABLE students
DROP INDEX uq_email;

-- Add a FOREIGN KEY
ALTER TABLE orders
ADD CONSTRAINT fk_student
FOREIGN KEY (student_id) REFERENCES students(id);

-- Remove the FOREIGN KEY
ALTER TABLE orders
DROP FOREIGN KEY fk_student;

Multiple Changes in One Statement

You can combine several alterations in a single ALTER TABLE statement by separating each action with a comma. This is more efficient than running multiple separate statements:

ALTER TABLE students
    ADD COLUMN phone VARCHAR(20) AFTER email,
    MODIFY COLUMN age TINYINT UNSIGNED,
    DROP COLUMN city;

Key Points to Remember

ADD COLUMN — adds a new column; use AFTER or FIRST to control position.
DROP COLUMN — permanently removes a column and all its data.
MODIFY COLUMN — changes data type or constraints; full definition must be re-specified.
RENAME COLUMN — renames a column (MySQL 8.0+); use CHANGE for older versions.
RENAME TO — renames the table itself.
Combine multiple changes in one statement using commas — more efficient than separate statements.

« Previous Lesson Next Lesson »