MySQL ALTER TABLE Statement
The ALTER TABLE statement in MySQL is crucial for adjusting the structure of tables, including adding or deleting columns, altering column types, and renaming tables, a vital tool for database schema updates. Let's discuss ALTER TABLE Statement in detail.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
MySQL ALTER TABLE Statement
Given below are the scenarios where the ALTER TABLE in MySQL needs to be used. Each one of these comprises different parameters to use along with the MySQL statement.
We can have a table of employees working in an organization. To begin with, there can be minimal data to show the information and the other ones can be added/removed in the later sections.
Here's the query in MySQL to create the table with emp_id, first_name, age, and dept with each of the records having emp_id as their PRIMARY KEY.
ALTER TABLE - ADD COLUMN
The ADD clause in MySQL is used to add a new column to the existing table. In order to add a new column, the name of the column, its data type, and any other necessary attribute (like specifying the size of the data that the column can hold) should be specified in the ALTER TABLE statement.
Syntax:
Example : We can add a new column that contains the last name of all the employees in an organization with the column name last_name. So, we should specify the last name can have a maximum of 15 characters.
Let's insert some queries to see how the data is displayed from the table.
Output:
| emp_id | first_name | age | dept | last_name |
|---|---|---|---|---|
| 1 | Jane | 30 | Sales | Miller |
| 2 | John | 27 | Marketing | Doe |
Since the column last_name was introduced after the table had been created, it is being added to the end of the table by default.
There are specifiers like FIRST and AFTER that can help you specify the position of a column in a table. If you wish to put a column at the very beginning, then use FIRST to put it first in the table or you can specify the name of any column after which it should be placed in the table.
Here's the syntax for the ALTER TABLE to use along with the specifiers in the ADD clause.
Example: Let's add a new column salary in the employees table.
Output:
| emp_id | first_name | age | dept | salary | last_name |
|---|---|---|---|---|---|
| 1 | Jane | 30 | Sales | 70000 | Miller |
| 2 | John | 27 | Marketing | 70000 | Doe |
Here's one additional example to show how you can add multiple columns to a table by using ALTER TABLE in MySQL.
ALTER TABLE - DROP COLUMN
Actually, no one wishes their salary to be so transparent to everyone and neither do these employees as well. So, it's good to remove the entire column salary from the table. We can use the DROP clause with the ALTER TABLE in MySQL to drop a column from the table.
Syntax:
Use the query given below to drop the column salary from the table.
Output:
| emp_id | first_name | age | dept | last_name |
|---|---|---|---|---|
| 1 | Jane | 30 | Sales | Miller |
| 2 | John | 27 | Marketing | Doe |
Note: If a column has a PRIMARY KEY or FOREIGN KEY constraint, it cannot be dropped using the ALTER TABLE statement. You will need to drop the constraint first before dropping the column.
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ moreALTER TABLE - MODIFY COLUMN
MODIFY COLUMN is used to change the data type or length of an existing column. You need to specify the name of the column and the new data type or length.
Syntax to modify the data type of a column:
Note: While using MODIFY COLUMN with ALTER TABLE in MySQL, the data type of the column cannot be changed to a type that isn't compatible with the existing data in the column.
We can also reposition a column in a table by using the MODIFY COLUMN clause. The same set of specifiers (FIRST | AFTER) can be applied to MODIFY COLUMN to reposition an already existing column in a table.
In our example, we can put the column last_name beside first_name. And we should also modify it to have a maximum of 20 characters.
Output:
| emp_id | first_name | last_name | age | dept |
|---|---|---|---|---|
| 1 | Jane | Miller | 30 | Sales |
| 2 | John | Doe | 27 | Marketing |
ALTER TABLE - RENAME COLUMN
RENAME COLUMN is used to rename an existing column in the table. You need to specify the current name of the column and the new name you want to give it.
Syntax:
Example:
Output:
| emp_id | first_name | last_name | age | department |
|---|---|---|---|---|
| 1 | Jane | Miller | 30 | Sales |
| 2 | John | Doe | 27 | Marketing |
Turn Learning into Career Growth
ALTER TABLE - RENAME TABLE
We can also rename the entire table by just specifying the current name of the table and the new name that we wish to set for it. The RENAME TABLE clause can be used to rename a table.
Syntax:
Example:
Output:
| emp_id | first_name | last_name | age | department |
|---|---|---|---|---|
| 1 | Jane | Miller | 30 | Sales |
| 2 | John | Doe | 27 | Marketing |
Conclusion
- ALTER TABLE in MySQL provides us with a flexible and convenient way to modify the structure of a table in MySQL.
- It provides a variety of clauses that can be used to add (ADD COLUMN), drop (DROP COLUMN), modify (MODIFY COLUMN), and rename columns (RENAME COLUMN) and tables (RENAME TABLE).
- When altering a table, it is important to consider the impact it may have on any existing data that rely on the table structure.