Guide to ALTER TABLE in MySQL

The Complete Guide to ALTER TABLE in MySQL: A Comprehensive Explanation MySQL ALTER TABLE is a powerful command used to modify an existing table structure without deleting or recreating it. This guide covers all aspects of ALTER TABLE, including adding, deleting, modifying columns, constraints, indexes, renaming tables, and much more, with real-world examples, SQL queries, and sample output. 1. Understanding ALTER TABLE in MySQL The ALTER TABLE statement allows you to change the structure of a table dynamically. ✅ Use Cases of ALTER TABLE Adding new columns to an existing table. Modifying data types or attributes of existing columns. Renaming columns or tables. Adding or removing constraints (e.g., PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK). 2. Basic Syntax of ALTER TABLE ALTER TABLE table_name action; Where action can be: ADD COLUMN column_name data_type – Add a new column. DROP COLUMN column_name – Remove an existing column. MODIFY COLUMN column_name new_data_type – Change a column’s data type. CHANGE COLUMN old_name new_name new_data_type – Rename a column. RENAME TO new_table_name – Rename the table. ADD CONSTRAINT or DROP CONSTRAINT – Manage constraints. 3. Adding Columns to a Table ✅ Adding a Single Column ALTER TABLE Employees ADD COLUMN age INT; ✅ Adding Multiple Columns ALTER TABLE Employees ADD COLUMN address VARCHAR(255), ADD COLUMN phone_number VARCHAR(15); 4. Dropping Columns from a Table ✅ Dropping a Single Column ALTER TABLE Employees DROP COLUMN age; ✅ Dropping Multiple Columns ALTER TABLE Employees DROP COLUMN address, DROP COLUMN phone_number; 5. Modifying Columns in a Table ✅ Changing Data Type ALTER TABLE Employees MODIFY COLUMN salary DECIMAL(12,2); ✅ Changing Default Value ALTER TABLE Employees ALTER COLUMN salary SET DEFAULT 5000; ✅ Removing Default Value ALTER TABLE Employees ALTER COLUMN salary DROP DEFAULT; 6. Renaming a Column or Table ✅ Renaming a Column ALTER TABLE Employees CHANGE COLUMN name full_name VARCHAR(100); ✅ Renaming a Table ALTER TABLE Employees RENAME TO Staff; 7. Adding and Removing Constraints 7.1 PRIMARY KEY Constraint ✅ Adding a PRIMARY KEY (Single Column) ALTER TABLE Employees ADD PRIMARY KEY (employee_id); ✅ Adding a PRIMARY KEY (Multiple Columns) ALTER TABLE Employees ADD CONSTRAINT pk_emp PRIMARY KEY (employee_id, department_id); ✅ Removing a PRIMARY KEY ALTER TABLE Employees DROP PRIMARY KEY; 7.2 UNIQUE Constraint ✅ Adding a UNIQUE Constraint (Single Column) ALTER TABLE Employees ADD CONSTRAINT unique_email UNIQUE (email); ✅ Adding a UNIQUE Constraint (Multiple Columns) ALTER TABLE Employees ADD CONSTRAINT unique_emp UNIQUE (first_name, last_name, department_id); ✅ Removing a UNIQUE Constraint ALTER TABLE Employees DROP INDEX unique_email; 7.3 FOREIGN KEY Constraint ✅ Adding a FOREIGN KEY (Single Column) ALTER TABLE Employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES Departments(department_id); ✅ Adding a FOREIGN KEY (Multiple Columns) ALTER TABLE Employees ADD CONSTRAINT fk_project FOREIGN KEY (project_id, department_id) REFERENCES Projects(project_id, department_id); ✅ Removing a FOREIGN KEY ALTER TABLE Employees DROP FOREIGN KEY fk_department; 7.4 CHECK Constraint ✅ Adding a CHECK Constraint (Single Column) ALTER TABLE Employees ADD CONSTRAINT chk_salary CHECK (salary > 0); ✅ Adding a CHECK Constraint with Another Column ALTER TABLE Employees ADD CONSTRAINT chk_bonus CHECK (bonus

Mar 6, 2025 - 18:21

The Complete Guide to ALTER TABLE in MySQL: A Comprehensive Explanation

MySQL ALTER TABLE is a powerful command used to modify an existing table structure without deleting or recreating it. This guide covers all aspects of ALTER TABLE, including adding, deleting, modifying columns, constraints, indexes, renaming tables, and much more, with real-world examples, SQL queries, and sample output.

1. Understanding ALTER TABLE in MySQL

The ALTER TABLE statement allows you to change the structure of a table dynamically.

✅ Use Cases of ALTER TABLE

Adding new columns to an existing table.
Modifying data types or attributes of existing columns.
Renaming columns or tables.
Adding or removing constraints (e.g., PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK).

2. Basic Syntax of ALTER TABLE

ALTER TABLE table_name action;

Where action can be:

ADD COLUMN column_name data_type – Add a new column.
DROP COLUMN column_name – Remove an existing column.
MODIFY COLUMN column_name new_data_type – Change a column’s data type.
CHANGE COLUMN old_name new_name new_data_type – Rename a column.
RENAME TO new_table_name – Rename the table.
ADD CONSTRAINT or DROP CONSTRAINT – Manage constraints.

3. Adding Columns to a Table

✅ Adding a Single Column

ALTER TABLE Employees
ADD COLUMN age INT;

✅ Adding Multiple Columns

ALTER TABLE Employees
ADD COLUMN address VARCHAR(255),
ADD COLUMN phone_number VARCHAR(15);

4. Dropping Columns from a Table

✅ Dropping a Single Column

ALTER TABLE Employees
DROP COLUMN age;

✅ Dropping Multiple Columns

ALTER TABLE Employees
DROP COLUMN address,
DROP COLUMN phone_number;

5. Modifying Columns in a Table

✅ Changing Data Type

ALTER TABLE Employees
MODIFY COLUMN salary DECIMAL(12,2);

✅ Changing Default Value

ALTER TABLE Employees
ALTER COLUMN salary SET DEFAULT 5000;

✅ Removing Default Value

ALTER TABLE Employees
ALTER COLUMN salary DROP DEFAULT;

6. Renaming a Column or Table

✅ Renaming a Column

ALTER TABLE Employees
CHANGE COLUMN name full_name VARCHAR(100);

✅ Renaming a Table

ALTER TABLE Employees
RENAME TO Staff;

7. Adding and Removing Constraints

7.1 PRIMARY KEY Constraint

✅ Adding a PRIMARY KEY (Single Column)

ALTER TABLE Employees
ADD PRIMARY KEY (employee_id);

✅ Adding a PRIMARY KEY (Multiple Columns)

ALTER TABLE Employees
ADD CONSTRAINT pk_emp PRIMARY KEY (employee_id, department_id);

✅ Removing a PRIMARY KEY

ALTER TABLE Employees
DROP PRIMARY KEY;

7.2 UNIQUE Constraint

✅ Adding a UNIQUE Constraint (Single Column)

ALTER TABLE Employees
ADD CONSTRAINT unique_email UNIQUE (email);

✅ Adding a UNIQUE Constraint (Multiple Columns)

ALTER TABLE Employees
ADD CONSTRAINT unique_emp UNIQUE (first_name, last_name, department_id);

✅ Removing a UNIQUE Constraint

ALTER TABLE Employees
DROP INDEX unique_email;

7.3 FOREIGN KEY Constraint

✅ Adding a FOREIGN KEY (Single Column)

ALTER TABLE Employees
ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES Departments(department_id);

✅ Adding a FOREIGN KEY (Multiple Columns)

ALTER TABLE Employees
ADD CONSTRAINT fk_project FOREIGN KEY (project_id, department_id) 
REFERENCES Projects(project_id, department_id);

✅ Removing a FOREIGN KEY

ALTER TABLE Employees
DROP FOREIGN KEY fk_department;

7.4 CHECK Constraint

✅ Adding a CHECK Constraint (Single Column)

ALTER TABLE Employees
ADD CONSTRAINT chk_salary CHECK (salary > 0);

✅ Adding a CHECK Constraint with Another Column

ALTER TABLE Employees
ADD CONSTRAINT chk_bonus CHECK (bonus <= salary * 0.2);

✅ Adding a CHECK Constraint (Multiple Conditions)

ALTER TABLE Employees
ADD CONSTRAINT chk_work_hours CHECK (work_hours BETWEEN 20 AND 60);

✅ Removing a CHECK Constraint

ALTER TABLE Employees
DROP CONSTRAINT chk_salary;

7.5 DEFAULT Constraint

✅ Adding a DEFAULT Constraint (Single Column)

ALTER TABLE Employees
ALTER COLUMN status SET DEFAULT 'Active';

✅ Adding a DEFAULT Constraint Based on Another Column

Note: Some databases support computed columns, but for direct reference-based defaults, you might need triggers instead.

ALTER TABLE Employees
ALTER COLUMN bonus_percentage SET DEFAULT 0.05;

✅ Removing a DEFAULT Constraint

ALTER TABLE Employees
ALTER COLUMN status DROP DEFAULT;

Summary of ALTER TABLE Commands

Action	Command
Add Column	`ALTER TABLE table_name ADD COLUMN column_name data_type;`
Drop Column	`ALTER TABLE table_name DROP COLUMN column_name;`
Modify Column	`ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;`
Rename Column	`ALTER TABLE table_name CHANGE COLUMN old_name new_name new_data_type;`
Rename Table	`ALTER TABLE table_name RENAME TO new_table_name;`
Add Constraint	`ALTER TABLE table_name ADD CONSTRAINT constraint_name;`
Drop Constraint	`ALTER TABLE table_name DROP CONSTRAINT constraint_name;`

Conclusion

The ALTER TABLE statement in MySQL is an essential tool for modifying table structures dynamically. It allows you to add, remove, rename, and modify columns, apply constraints, and change table properties efficiently. Understanding these operations enables you to handle database schema changes effectively.