In the past, we showed you just how easy it is to create a table in MySQL. Tables are an essential building block in MySQL that help to form and sculpt the overall structure of your database. We noted that once you have created a database, the time comes when you need to determine what type of structure you want it to have. In other words, a few questions come to mind, such as what specific tables do you want your database to have? What type of information do you want each of these tables to store? Also, what columns do you want each of your tables to have? These are just some questions to ponder when determining your database layout in terms of tables.
Now, answering such questions right off the bat may be a bit difficult if you do not have a clear plan as to how you want your database to be organized. Sure, it is great to be very descriptive when creating columns and the like, but you do not want to do it in an excessive manner. So, keeping this in mind, what do you do if you decide that you need to change the structure or certain characteristics of your tables in MySQL at a later date? Do you have to create them all over again? The answer, thankfully, is no. Thanks to the ALTER TABLE statement, you can change a table’s structure in various ways.
For instance, in the case of columns, you can add them, delete them, or even change their types. Want to rename a column with a label that is more appropriate to your needs, you can do so with ALTER TABLE. Other examples of things you can do with the ALTER TABLE statement include renaming tables, creating indexes, destroying indexes, and the list goes on.
As for the format of the ALTER TABLE statement, here it is in basic form:
ALTER TABLE table_name alter_specification1 [,alter_specification 2,…]
A closer look at this syntax reveals that we begin with the ALTER TABLE statement itself. Next we list the name of the table whose structure or characteristics we want to change. After the table name, comes the specific alterations that we want to perform. To make life easier on you, MySQL allows you to execute multiple alterations at the same time. Just remember to separate each by a comma in the syntax.
You have many options when it comes to alter specifications, as mentioned earlier. You can get the full list of these options by visiting the official MySQL website and viewing the ALTER TABLE section of its online manual.
Now that you have a basic overview of the ALTER TABLE statement and its importance, it is time to take a look at a few examples ALTER TABLE in action. As is usually the case with tutorials, these are simple our own examples, so you can look them over to get an understanding of how they work and then apply them to your own tables.
Before we begin using ALTER TABLE, let’s create a sample table for the purposes of this tutorial.
CREATE TABLE ‘assignments’ (
‘assignment_id’ INT NOT NULL ,
‘topic’ VARCHAR(45) NULL ,
‘start_date’ DATETIME NULL ,
‘end_date’ DATETIME NULL ,
‘notes’ VARCHAR(200) NULL ,
PRIMARY KEY (‘assignment_id’) ,
UNIQUE INDEX ‘assignment_id_UNIQUE’ (‘assignment_id’ ASC) );
This created a table named assignments that we will use for our upcoming examples.
How to Use the ALTER TABLE Statement to rename a table in MySQL
For our first example, we will focus on renaming a table in MySQL via the ALTER TABLE statement. This is a task that you may need to accomplish for one reason or another when working in MySQL, so it is one worth learning. Luckily, it is very simple in its format, which is the following:
ALTER TABLE ‘assignments’
RENAME TO ‘duties’ ;
This changes the name of our assignments table to the name of duties.
How to Use the ALTER TABLE Statement to add a column to a table in MySQL
Since our table deals with assignments, or duties, as it is now called, let’s say we want to add a column that shows the progress of each of our duties. Here is how we can do so:
ALTER TABLE duties ADD COLUMN ‘progress’ DECIMAL(2,1) NULL
AFTER ‘notes’ ;
This alters the duties table by adding the progress column after the notes column.
How to Use the ALTER TABLE Statement to delete or drop a column from a table in MySQL
What if we want to do the opposite of adding a column to our table, and want to delete or drop one that we really do not need? Here is how:
ALTER TABLE duties
DROP COLUMN notes ;
This alters our duties table by dropping the notes column.
This concludes our tutorial on the ALTER TABLE statement. Earlier we mentioned the various tasks that could be executed via this statement to give you a glimpse into just how useful it can be when working in MySQL. To put its usefulness into action, we showed you examples on how to use the ALTER TABLE statement to rename a table, add a column to a table, and delete a column from a table. As you can see from our examples, using ALTER TABLE is quite simple, yet effective when you want to change the structure of your tables in MySQL.
There are obviously many more ways in which ALTER TABLE can be implemented, but hopefully the few examples in this tutorial served to give you a brief understanding of its functionality. With that basic knowledge now in your possession, you can research other ways in which you can make the ALTER TABLE statement work for you.
Feel free to leave any comments you may have on this tutorial in the comment section, and be sure to visit us once again at Dev Shed for more MySQL tutorials in the future.