MySQL ALTER TABLE

After a table is created, the structure may need to change over time — a new column might be required, an existing column might need a different data type, or a column might need to be removed entirely. The ALTER TABLE statement handles all these structural changes without deleting the table or its data.

Common Uses of ALTER TABLE

• Adding a new column
• Dropping (removing) an existing column
• Modifying a column's data type or size
• Renaming a column
• Renaming the table itself
• Adding or removing constraints and indexes

Adding a New Column

Use ADD to insert a new column into an existing table:

ALTER TABLE students
ADD phone VARCHAR(15);

By default, the new column is added at the end of the table. To add it after a specific column, use AFTER:

ALTER TABLE students
ADD phone VARCHAR(15) AFTER email;

To add it as the first column, use FIRST:

ALTER TABLE students
ADD phone VARCHAR(15) FIRST;

Dropping a Column

Use DROP COLUMN to remove a column permanently:

ALTER TABLE students
DROP COLUMN phone;

This removes the phone column and all its data from every row. This action cannot be undone.

Modifying a Column

Use MODIFY to change a column's data type, size, or constraints:

ALTER TABLE students
MODIFY email VARCHAR(200) NOT NULL;

This changes the email column to allow up to 200 characters and makes it required (NOT NULL).

Renaming a Column

Use RENAME COLUMN to give an existing column a new name:

ALTER TABLE students
RENAME COLUMN enrolled_on TO enrollment_date;

Changing a Column Name and Type Together

Use CHANGE to rename a column and modify its definition at the same time:

ALTER TABLE students
CHANGE age student_age SMALLINT;

Here, the column age is renamed to student_age and its type is changed to SMALLINT.

Renaming the Table

Use RENAME TO to rename the entire table:

ALTER TABLE students
RENAME TO learners;

Adding a Constraint

A constraint such as NOT NULL, UNIQUE, or a primary key can be added after the table is created:

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

Dropping a Constraint

To remove a constraint:

ALTER TABLE students
DROP INDEX unique_email;

Making Multiple Changes at Once

Multiple changes can be combined in a single ALTER TABLE statement using commas:

ALTER TABLE students
ADD phone VARCHAR(15),
MODIFY last_name VARCHAR(100) NOT NULL,
RENAME COLUMN enrolled_on TO enrollment_date;

Example Walkthrough

Starting with this table structure:

CREATE TABLE employees (
    emp_id    INT PRIMARY KEY,
    name      VARCHAR(100),
    dept      VARCHAR(50)
);

Now add a salary column, rename dept to department, and increase the name size:

ALTER TABLE employees
ADD salary DECIMAL(10, 2) DEFAULT 0.00,
RENAME COLUMN dept TO department,
MODIFY name VARCHAR(150) NOT NULL;

Key Points

• ALTER TABLE modifies the structure of an existing table without deleting its data.
• Use ADD to insert a new column, DROP COLUMN to remove one.
• Use MODIFY to change a column's type or constraints.
• Use RENAME COLUMN to rename a column and RENAME TO to rename the table.
• Multiple changes can be made in a single statement using commas.