MySQL ALTER TABLE Commands: How to Add, Delete, & Change Columns

MySQL (and MariaDB) allows you to change the structure of tables with the ALTER TABLE SQL command. Using the command, you can easily change the name of your table and columns, add or delete columns, or change the type of existing columns. Let’s see this in action.

Uses and Examples of ALTER TABLE

First, let’s create a database and a table, which we’ll respectively name “test” and “employees.” Here’s how you’d do that:

Rename a Table

The syntax for renaming a table is:

So, to change our employees table to “users,” execute the following:

Helpful hint: ALTER TABLE on its own does nothing. Start with the command and the table name, then specify the alterations to be made.

Add a Column and Change Column Properties

The syntax for adding a column to a table is:

To remove or delete a column:

Here, we will add a string column called “address” by typing:

Add a TIMESTAMP column named “date” by entering the following command:

Add an index on the column named “id” by running:

It is also possible to do multiple additions at once:

Our table should now look like this:

Modify a Column Type

The column modification syntax is:

To modify the address field to allow for larger strings:

We can also combine multiple modifications at once:

With this command, we also changed the column called “name” to “lastname” and disallowed NULL values for it.

Changing the Default Column Value

To change the default value on a column, use the SET DEFAULT syntax:

Our final table now looks like this:

Why Should You Use ALTER TABLE?

Making changes to a MySQL database structure via the command line and ALTER TABLE can sometimes be faster and more flexible than using PHPMyAdmin or similar GUI tools.

The command offers even more features that we didn’t cover here. For more information, check out the MySQL documentation or ask a question down in the comments section.

ABOUT THE AUTHOR
Ryan Frankel

Ryan Frankel has been a professional in the tech industry for more than 10 years and has been developing websites for more than 15. With his background in integrated circuit design and digital signal processing, he has a fundamental understanding of hardware systems and the software that runs them. Ryan now sits as the CTO of Digital Brands Inc. and manages all of the server infrastructure of their websites, as well as their development team. In addition, Ryan has a passion for guitars, good coffee, and puppies.