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 CONSTRAINTorDROP 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.
Top comments (0)