The ALTER TABLE statement in MySQL is used to modify the structure of an existing table without deleting or recreating it. It helps update database tables efficiently as requirements change while preserving existing data.
- It allows developers to add, modify, or delete columns and constraints as needed.
- It eliminates the need to recreate tables when changes are required, saving time and effort.
- It is an important Data Definition Language (DDL) command that helps adapt database design over time.
Syntax:
ALTER TABLE table_name
action;
Here, the action defines what kind of modification you want to perform, such as adding a column, modifying a column, or removing a constraint.
Working with ALTER TABLE Statement
ALTER TABLE is used to perform different structural modifications on an existing table such as adding, modifying, renaming, or deleting elements.
1. Adding New Columns
One of the most common uses of ALTER TABLE is adding new columns to an existing table. This is useful when new data requirements arise.
Syntax:
ALTER TABLE table_name
ADD COLUMN column_name data_type;
Query:
ALTER TABLE students
ADD COLUMN email VARCHAR(100);
- Adds a new column email to the table without affecting existing data.
- Existing rows will have NULL values unless a default value is specified.
2. Modifying Existing Columns
Modifying columns helps update data types or properties to match new requirements.
Syntax:
ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type;
Query:
ALTER TABLE students
MODIFY COLUMN age VARCHAR(3);
- Changes the data type of the age column.
- Ensure existing data is compatible with the new format.
3. Renaming Columns
In certain cases, column names may need to be updated to better reflect their purpose. The CHANGE keyword allows both renaming and redefining a column.
Syntax:
ALTER TABLE table_name
CHANGE COLUMN old_name new_name data_type;