Alter Column in MySQL

[Linux Hint]

While managing a database, a database administrator needs to alter the tables’ structure to stay up to date. In this article, we will learn some different types of tasks that we can do using MySQL’s provided ALTER command. So, let’s get started.

ALTER command alters or changes the structure of the table. For example,

• Addition of a column
• Deletion of a column
• Renaming of a column
• Modification of a column

In this article, we will try examples of ALTER commands to add, delete, rename, and modify the columns of a table in MySQL.

Add a column

We can add a column into an already existing table using the following syntax of the ALTER command.

ALTER TABLE table_name
ADD column_name datatype;

In this syntax, make sure to replace table_name and column_name with the name you want to give.

We can also mention the position of the newly created column by using the FIRST and AFTER clause and referencing the already existing column in the table. For example

ALTER TABLE table_name
ADD column_name datatype
[FIRST | AFTER] existing_column_name;

If we want to add a column before an existing column, we can use the FIRST clause. Similarly, if we want to add a column after the existing column, we can use the AFTER clause.

We can add multiple columns as well, using the same ALTER TABLE and ADD column syntax. For example

ALTER TABLE table_name
ADD column_name1 datatype
[FIRST | AFTER] existing_column_name
ADD column_name2 datatype
[FIRST | AFTER] existing_column_name;

Drop/Delete a column

Deleting a column in MySQL is as easy as saying. We can remove an existing column in a table by using the ALTER TABLE command and DROP. The syntax for deleting a column is

ALTER TABLE table_name
DROP column_name;

This is how simple it is to delete a column of a table in MySQL.

Rename a Column

We can rename a column by using the CHANGE clause along with the ALTER TABLE command. In which, we first provide the existing name of the column and then the new name along with the data type, followed by the CHANGE clause. To change the name of the column, run the following command

ALTER TABLE table_name
CHANGE COLUMN previous_column_name new_column_name datatype;

If we can change the name of the column, we can change the name of the table and use the RENAME clause with the ALTER TABLE command. The syntax for renaming the table is

ALTER TABLE table_name
RENAME TO new_table_name;

Modify a column

Suppose we want to change the column definition or data type of a column in MySQL. The MODIFY clause comes in handy to help us in this case. We can use the MODIFY clause to alter the data type of a column. Like this

ALTER TABLE table_name
MODIFY cloumn_name new_data_type;

Pro-Tip

Here is a protip for using the ALTER command.

Set default value to a column

If we want to provide or set some default value to a column of a table. WE can do so by running the following command

ALTER TABLE table_name
ALTER cloumn_name SET DEFAULT value;

In this syntax, make sure to replace the table_name, column_name, and value according to your requirement.

If we can set the default values of a column. There must be a way to unset or drop the default values of a column.

Drop default value of a column

Well, MySQL provides the DROP DEFAULT clause as well in the ALTER TABLE command to drop the default values of a column.

ALTER TABLE table_name
ALTER cloumn_name DROP DEFAULT;

So, these are some of the different ways to manage and alter the table in MySQL.

Summary

This article includes various ways to use the ALTER command to perform some different operations. In this article, we learned to add, delete, rename the table and columns, modify the column definition, set and unset the default values of a column using the ALTER TABLE command in MySQL. If this article helped you in having a better understanding of ALTER Command in MySQL and you want to learn more about the concepts of MySQL. Keep visiting our website linuxhint.com.

View the full article