ALTER TABLE operations in MySQL

Introduction

ALTER TABLE lets you change a table's structure without deleting data. You can add, remove, or modify columns easily.

You want to add a new column to store extra information.

You need to remove a column that is no longer needed.

You want to change the data type of a column to fit new data.

You want to rename a column for better clarity.

You want to add or drop constraints like primary keys or indexes.

Syntax

MySQL

ALTER TABLE table_name
  ADD column_name datatype;

ALTER TABLE table_name
  DROP COLUMN column_name;

ALTER TABLE table_name
  MODIFY COLUMN column_name new_datatype;

ALTER TABLE table_name
  RENAME COLUMN old_name TO new_name;

You can perform one or multiple changes in a single ALTER TABLE statement by separating them with commas.

Always back up your data before altering table structures to avoid accidental data loss.

Examples

Adds a new column 'birthdate' of type DATE to the 'employees' table.

MySQL

ALTER TABLE employees ADD birthdate DATE;

Removes the 'birthdate' column from the 'employees' table.

MySQL

ALTER TABLE employees DROP COLUMN birthdate;

Changes the 'salary' column to have a decimal type with 10 digits total and 2 decimal places.

MySQL

ALTER TABLE employees MODIFY COLUMN salary DECIMAL(10,2);

Renames the column 'salary' to 'monthly_salary' for better clarity.

MySQL

ALTER TABLE employees RENAME COLUMN salary TO monthly_salary;

Sample Program

This creates a simple 'employees' table, then adds a 'birthdate' column, changes 'salary' to a decimal type, renames 'name' to 'full_name', and finally shows the table structure.

MySQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  salary INT
);

ALTER TABLE employees ADD birthdate DATE;

ALTER TABLE employees MODIFY COLUMN salary DECIMAL(10,2);

ALTER TABLE employees RENAME COLUMN name TO full_name;

DESCRIBE employees;

Important Notes

Some ALTER TABLE operations can lock the table and take time on large tables.

Not all changes are allowed on every database engine; check your MySQL version and engine.

Renaming columns is supported in MySQL 8.0+; older versions use CHANGE COLUMN syntax.

Summary

ALTER TABLE changes table structure without losing data.

You can add, drop, modify, or rename columns.

Always check your MySQL version and back up data before altering tables.