MySQL ALTER TABLE

ALTER TABLE is used to change the structure of an existing table.

ALTER TABLE students
ADD mobile VARCHAR(15);

This adds a new mobile column to the students table.

The basic syntax is:

ALTER TABLE table_name
operation;

The operation specifies what change you want to make to the table.

The ADD COLUMN operation adds a new column.

ALTER TABLE students
ADD COLUMN mobile VARCHAR(15);

The students table now contains a mobile column.

You can add multiple columns in one ALTER TABLE statement.

ALTER TABLE students
ADD COLUMN email VARCHAR(150),
ADD COLUMN address VARCHAR(255);

Both email and address columns are added.

A new column can be added with a default value.

ALTER TABLE students
ADD COLUMN status VARCHAR(20)
DEFAULT 'Active';

The new column uses Active as its default value when no other value is provided.

You can define constraints while adding a column.

ALTER TABLE students
ADD COLUMN city VARCHAR(100) NOT NULL;

Existing data must be compatible with the new constraint and MySQL's handling of the new column.

ALTER TABLE can be used to modify a column to use AUTO_INCREMENT when the table design and key requirements allow it.

ALTER TABLE students
MODIFY COLUMN id INT AUTO_INCREMENT;

AUTO_INCREMENT is commonly used with integer key columns.

The MODIFY COLUMN operation changes a column's definition.

ALTER TABLE students
MODIFY COLUMN name VARCHAR(150);

This changes the maximum length of the name column to 150 characters.

MODIFY COLUMN can be used to change the data type.

ALTER TABLE students
MODIFY COLUMN age BIGINT;

The age column is changed from its previous definition to BIGINT.

You can add a NOT NULL constraint while modifying a column.

ALTER TABLE students
MODIFY COLUMN name VARCHAR(100) NOT NULL;

The name column is changed to require a value.

You can allow NULL values by modifying the column without the NOT NULL constraint.

ALTER TABLE students
MODIFY COLUMN email VARCHAR(150) NULL;

The email column is allowed to contain NULL values.

The CHANGE COLUMN operation can rename a column and define its data type.

ALTER TABLE students
CHANGE COLUMN mobile phone VARCHAR(15);

The mobile column is renamed to phone.

MySQL also supports RENAME COLUMN for renaming a column.

ALTER TABLE students
RENAME COLUMN phone TO mobile;

This changes the column name from phone back to mobile without specifying the data type.

The DROP COLUMN operation removes a column from a table.

ALTER TABLE students
DROP COLUMN address;

The address column is removed from the table.

Multiple columns can be removed using multiple DROP COLUMN clauses.

ALTER TABLE students
DROP COLUMN address,
DROP COLUMN city;

Both columns are removed.

ALTER TABLE can also rename a table.

ALTER TABLE students
RENAME TO student_records;

The table name changes from students to student_records.

MySQL also provides the RENAME TABLE statement.

RENAME TABLE student_records
TO students;

This changes the table name back to students.

ALTER TABLE can be used to add a primary key.

ALTER TABLE students
ADD PRIMARY KEY (id);

The id column becomes the primary key.

A primary key can be removed using ALTER TABLE.

ALTER TABLE students
DROP PRIMARY KEY;

This removes the primary key constraint from the table.

You can add a UNIQUE constraint to prevent duplicate values.

ALTER TABLE students
ADD UNIQUE (email);

This prevents duplicate email values according to the constraint's NULL rules.

To remove a unique constraint, you normally drop its index.

ALTER TABLE students
DROP INDEX email;

The exact index name should be checked before dropping it.

ALTER TABLE can be used to create an index.

ALTER TABLE students
ADD INDEX idx_course (course);

This creates an index named idx_course on the course column.

An index can be removed using DROP INDEX.

ALTER TABLE students
DROP INDEX idx_course;

The idx_course index is removed.

ALTER TABLE can add a foreign key that creates a relationship with another table.

ALTER TABLE students
ADD CONSTRAINT fk_course
FOREIGN KEY (course_id)
REFERENCES courses(id);

The foreign key connects course_id in students with id in courses.

A foreign key constraint can be removed by its constraint name.

ALTER TABLE students
DROP FOREIGN KEY fk_course;

The relationship represented by that foreign key constraint is removed.

• Using the wrong column name
• Changing a data type without checking existing data
• Dropping a column containing important information
• Dropping an index that is required by a constraint
• Adding NOT NULL when existing rows contain NULL
• Changing a primary key without understanding relationships
• Forgetting the correct foreign key or constraint name

Before or after ALTER TABLE, you can inspect the table structure.

DESCRIBE students;

You can also use:

SHOW CREATE TABLE students;

These commands help you understand the current table definition.

A safe ALTER TABLE workflow is:

1. Check the existing table structure.
2. Understand the data and constraints.
3. Choose the required ALTER operation.
4. Execute the ALTER TABLE statement.
5. Check the table structure again.
6. Test queries that use the modified table.

DESCRIBE students;

ALTER TABLE students
ADD COLUMN mobile VARCHAR(15);

DESCRIBE students;

Suppose an existing students table needs email and mobile columns.

ALTER TABLE students
ADD COLUMN email VARCHAR(150),
ADD COLUMN mobile VARCHAR(15);

Later, suppose the name column needs a larger size:

ALTER TABLE students
MODIFY COLUMN name VARCHAR(150);

These operations modify the existing table without recreating it.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    course VARCHAR(100),
    age INT,
    fee DECIMAL(10,2)
);

-- Add a column
ALTER TABLE students
ADD COLUMN email VARCHAR(150);

-- Add another column
ALTER TABLE students
ADD COLUMN mobile VARCHAR(15);

-- Modify a column
ALTER TABLE students
MODIFY COLUMN name VARCHAR(150);

-- Rename a column
ALTER TABLE students
RENAME COLUMN mobile TO phone;

-- Add an index
ALTER TABLE students
ADD INDEX idx_course (course);

-- Check the final structure
DESCRIBE students;

This example demonstrates several common ALTER TABLE operations: adding columns, modifying a column, renaming a column, adding an index, and checking the final table structure.