Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
ADD a Column in the Table
2.1.
Syntax
2.2.
Parameters:
2.3.
Example
3.
Add Multiple Columns in the Table
3.1.
Syntax
3.2.
Parameters
3.3.
Example
4.
MODIFY Column in the Table
4.1.
Syntax
4.2.
Parameters
4.3.
Example
5.
DROP Column in Table
5.1.
Syntax
5.2.
Parameters:
5.3.
Example
6.
RENAME Column in Table
6.1.
Syntax
6.2.
Parameters
6.3.
Example
7.
RENAME Table
7.1.
Syntax
7.2.
Parameters
7.3.
Example
8.
Frequently Asked Questions
8.1.
Can I revert a column name back to its original after renaming it?
8.2.
What happens to the data in a column when it's modified?
8.3.
Is it possible to add, modify, and drop columns in a single ALTER TABLE command?
9.
Conclusion
Last Updated: Mar 31, 2024
Easy

Alter Command in Mysql

Author Gaurav Gandhi
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

Managing a database effectively is crucial for any application's success. One of the key tasks in database management is modifying the database structure to meet evolving requirements. This is where the ALTER command in MySQL comes into play. It's a powerful tool that allows us to make changes to the database tables, such as adding or removing columns, changing data types, and renaming tables or columns. 

Alter Command in Mysql

Throughout this article, we'll explore various aspects of the ALTER command, including adding columns, modifying columns, and renaming tables, to name a few. 

ADD a Column in the Table

One of the common tasks when working with databases is adding new columns to existing tables. This can be necessary when you need to store additional information that wasn't originally planned for. MySQL makes this task simple with the ALTER command.

Syntax

ALTER TABLE table_name ADD column_name datatype;

Parameters:

  • table_name: The name of the table where you want to add a new column.
     
  • column_name: The name you want to give to the new column.
     
  • datatype: The type of data that the new column will hold, like INT, VARCHAR, DATE, etc.

Example

Imagine we have a table named Students that stores student ID and name. We now want to add an email column to store the students' email addresses.

ALTER TABLE Students ADD email VARCHAR(255);


This command adds a new column named email to the Students table, capable of holding strings up to 255 characters long, which is typical for email addresses.

This way of adding columns ensures your database can evolve & accommodate new data requirements without disrupting existing structures.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Add Multiple Columns in the Table

As your database grows, you might find yourself needing to add more than one column to a table at the same time. This could be due to new features in your application or changes in data requirements. MySQL's ALTER command is versatile enough to handle this task efficiently.

Syntax

ALTER TABLE table_name
ADD column1_name datatype1,
ADD column2_name datatype2, ...;

Parameters

  • table_name: The name of the table you're modifying.
     
  • column1_name, column2_name, ...: The names of the new columns you're adding.
     
  • datatype1, datatype2, ...: The data types for each new column, specifying what kind of data they will store.

Example

Let's say our Students table now needs two more columns: phone_number to store the students' contact numbers and birthdate to store their dates of birth.

ALTER TABLE Students
ADD phone_number VARCHAR(15),
ADD birthdate DATE;


This command will add two new columns to the Students table. The phone_number column is set to hold strings up to 15 characters long, which is sufficient for most international phone numbers. The birthdate column is set to hold date values, allowing for the storage of students' birth dates.

Adding multiple columns at once like this keeps your database modifications neat and reduces the amount of time the table is locked for alterations, which is especially important for large databases or those with high transaction volumes.

MODIFY Column in the Table

Sometimes, the initial setup of a table does not fully meet the later requirements of your application. You might need to change a column's data type to accommodate larger numbers, longer text, or a different format altogether. The MODIFY keyword in the ALTER command lets you make these kinds of adjustments without losing existing data.

Syntax

ALTER TABLE table_name MODIFY column_name new_datatype;

Parameters

  • table_name: The name of the table you're working on.
     
  • column_name: The name of the column you want to modify.
     
  • new_datatype: The new data type you want to assign to the column.

Example

Suppose in our Students table, we initially set the phone_number column to a VARCHAR(15), thinking it would be enough. Later, we realize some international numbers might not fit into 15 characters, so we decide to expand this column.

ALTER TABLE Students MODIFY phone_number VARCHAR(20);


This command changes the phone_number column's data type in the Students table to VARCHAR(20), allowing for phone numbers up to 20 characters long. This modification ensures that all potential international numbers can be accommodated, enhancing the database's flexibility and utility.

Modifying columns like this allows databases to adapt to changing needs without requiring a complete overhaul, making database management more efficient and less error-prone.

DROP Column in Table

There are times when certain columns in a table become unnecessary or irrelevant due to changes in your application's requirements or data structure. Removing these columns helps keep your database clean and efficient. The DROP COLUMN functionality of the ALTER command allows you to remove columns from a table.

Syntax

ALTER TABLE table_name DROP COLUMN column_name;

Parameters:

  • table_name: The name of the table from which you want to remove a column.
     
  • column_name: The name of the column you wish to drop.

Example

Imagine that we initially had a hobby column in our Students table to store students' hobbies. Over time, we find this information isn't used and decide to remove the column to streamline the table.

ALTER TABLE Students DROP COLUMN hobby;


Executing this command will remove the hobby column from the Students table. It's important to note that dropping a column will delete all the data stored in that column, so it's a good idea to make sure this data is either backed up or no longer needed before you proceed.

Dropping unnecessary columns simplifies the database structure, making it easier to manage and potentially improving performance by reducing the amount of data stored.

RENAME Column in Table

There might come a time when a column's name in your database no longer reflects the data it holds or is not consistent with the naming conventions you want to implement. Renaming a column can make your database more intuitive and maintainable. The ALTER command in MySQL includes a RENAME COLUMN operation specifically for this purpose.

Syntax

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Parameters

  • table_name: The name of the table where the column you want to rename is located.
     
  • old_column_name: The current name of the column you wish to rename.
     
  • new_column_name: The new name you want to give to the column.

Example

Suppose in our Students table, there's a column named enrolment_date, but we realize that enrollment_date (with double 'l') is a more common spelling and matches our other tables better.

ALTER TABLE Students RENAME COLUMN enrolment_date TO enrollment_date;


This command changes the name of the enrolment_date column to enrollment_date in the Students table. It's a simple change that can significantly improve the consistency and readability of your database schema.

Renaming columns is a powerful feature, but it's essential to update any database queries, applications, or reports that rely on the old column name to prevent errors.

RENAME Table

As your application grows and changes, you may find the need to rename entire tables to better reflect their contents or to fit within a new naming convention. This is not uncommon, especially during significant updates or overhauls of your application. MySQL facilitates this through the ALTER TABLE command, allowing you to rename a table efficiently without affecting the data stored within it.

Syntax

RENAME TABLE old_table_name TO new_table_name;

Parameters

  • old_table_name: The current name of the table you wish to rename.
     
  • new_table_name: The new name you want to assign to the table.

Example

Imagine our database has a table named Student_Info, but to maintain consistency with other table names which are all singular, we decide to rename it to Student.

RENAME TABLE Student_Info TO Student;


By executing this command, the Student_Info table will be renamed to Student. This operation is quick and preserves all the data within the table, making it a seamless transition to the new name.

Renaming tables is a straightforward task, but it's crucial to remember to update any references to the old table name in your application code, queries, and reports to ensure everything continues to function correctly.

Frequently Asked Questions

Can I revert a column name back to its original after renaming it?

Yes, you can rename a column back to its original name using the same ALTER TABLE RENAME COLUMN command. Just ensure your database doesn't have a new column with the old name.

What happens to the data in a column when it's modified?

Modifying a column's data type changes how the data is stored, but MySQL tries to convert existing data to the new type. If the data can't be converted, it might result in data loss, so always back up your data first.

Is it possible to add, modify, and drop columns in a single ALTER TABLE command?

Yes, you can combine multiple operations in a single ALTER TABLE command. This is efficient as it reduces the number of times the table needs to be locked for changes, but ensure you order operations carefully to avoid conflicts.

Conclusion

In this article, we've learned about the versatile ALTER command in MySQL, covering how to add new columns, modify existing ones, drop unwanted columns, and even rename them for better clarity. These operations are crucial for maintaining and updating your database to keep up with changing data requirements. By understanding and applying these commands, you can ensure your database remains organized, efficient, and aligned with your application's needs.

You can refer to our guided paths on the Coding Ninjas. You can check our course to learn more about DSADBMSCompetitive ProgrammingPythonJavaJavaScript, etc. Also, check out some of the Guided Paths on topics such as Data Structure and AlgorithmsCompetitive ProgrammingOperating SystemsComputer Networks, DBMSSystem Design, etc., as well as some Contests, Test Series, and Interview Experiences curated by top Industry Experts.

Previous article
MySQL ROWCOUNT
Next article
MySQL Variables
Live masterclass