MySQL ALTER Command

By /
MySQL ALTER Command

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 – CREATE, RENAME, and DROP.

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);Code language: SQL (Structured Query Language) (sql)

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.

Alter Employee Table
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:

ALTER TABLE table_name ADD column_name data_type;Code language: SQL (Structured Query Language) (sql)

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);Code language: SQL (Structured Query Language) (sql)

The output should look as follows,

MySQL ALTER Command Single Column

2. Adding multiple columns

The MySQL 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);Code language: SQL (Structured Query Language) (sql)

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);Code language: SQL (Structured Query Language) (sql)

Your output will look something like,

Alter Command Add Multiple Columns 1

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);Code language: SQL (Structured Query Language) (sql)

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');Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Alter Add Column With Default Value

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;Code language: SQL (Structured Query Language) (sql)

Here’s an example of it. We delete the Email column in the above ‘Employee’ table.

ALTER TABLE Employee DROP COLUMN Email;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Alter Drop Column

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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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,

Alter Command Modify Column

Conclusion

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.

References