The MySQL ALTER command is used to modify an existing table. It forms a part of the Data Definition Language (DDL) in SQL, which deals with the definition of data. Other commands in DDL are –
When you go buy pieces of clothing, I’m sure you must have come across a situation in which the size isn’t apt to the kind of fitting you want. In such a case, you give that apparel to a tailor and ask him to alter it. The end product you get is a modified version of the previous apparel, suited to your size.
Similarly, if you make a table in SQL, chances are, you may realize later that you want certain changes in the table. SQL, like the tailor and a beautiful language that it is, gives you the chance to alter a table.
Syntax of MySQL ALTER
ALTER TABLE table_name [ADD | DROP | MODIFY]( column_name1 data_type1, column-name2 data_type2, ... column-nameN data_typeN);
There is so much that we can do with the ALTER statement. To explore the syntax and the usage of ALTER in detail, let us have a look at a few examples.
Examples of MySQL ALTER
Let us now explore the different ways in which you can alter a table. For our reference, we use the following ‘Employee’ table.
Adding columns using ALTER
Let’s begin by adding new columns to an existing table. When you define a table with the CREATE table command, you may not know which column you’ll need in the future. And this is when the MySQL ALTER command comes into the picture.
1. Adding a single column
The following syntax is used to add a column in a table using MySQL
ALTER TABLE table_name ADD column_name data_type;
For example, if we want to add the column ‘Designation’ in the above employee table, we will do the following:
ALTER TABLE Employee ADD Designation varchar(255);
The output should look as follows,
2. Adding multiple columns
ALTER command does not limit you to add only a single column. You may add multiple columns at a time by referring to the following syntax:
ALTER TABLE table_name ADD( column_name1 data_type1, column-name2 data_type2, column-name3 data_type3);
So, now if you wish to add two columns named ‘Email’ and ‘DOB’ in the above ‘Employee’ table, the SQL query will be,
ALTER TABLE Employee ADD( Email varchar(255), DOB date);
Your output will look something like,
3. Adding a column with a default value
You may also add a column with a preset default value. Here’s the syntax for it:
ALTER TABLE table_name ADD( column_name data_type DEFAULT default_value);
Here’s an example of the same. We add a Department column and set its default value to ‘Executive’.
ALTER TABLE Employee ADD(Department varchar(255) DEFAULT 'Executive');
And we get the output as,
Dropping a column using ALTER
We can also drop or delete a column using
ALTER. Here’s the syntax for it:
ALTER TABLE table_name DROP COLUMN column_name;
Here’s an example of it. We delete the Email column in the above ‘Employee’ table.
ALTER TABLE Employee DROP COLUMN Email;
And we get the output as,
Modifying existing columns in a table
Finally, we can modify the data type of the existing columns in our table using the
ALTER command. Here’s the syntax for it:
ALTER TABLE table_name MODIFY column_name data_type;
Here’s an example of changing the datatype of the City column from varchar(255) to text type in MariaDB. The text data type supports a maximum length of 65,535 characters.
ALTER TABLE Employee MODIFY City text;
We use the DESC command to see the change made for the data type. The
DESC command shows you the structure of the table. We get the output as,
In conclusion, as you can see, MySQL
ALTER command is very powerful and will help you a lot while querying with SQL. I hope you have fun exploring it. For further reference, you may go through the below links.