Altering Tables - Learning MySQL and MariaDB - page 70

Essential Changes
After you have created a table,entered data into it, and begun to use it, you will invariably
need to make changes to the table. You may need to add another column, change the data
type of the column (e.g., to allow for more characters), or perhaps rename a column for
clarity of purpose or to align the columns better with columns in other tables. To improve
the speed at which data is located in the column (i.e., make queries faster), you might want
to add or change an index. You may want to change one of the default values or set one of
the options. All of these changes can be made through the ALTER TABLE statement.
The basic syntaxfor the ALTER TABLE is simple:
ALTER TABLE table_name changes ;
Replace table_name with the name of the table you want to change. Enter the changes
you want to make on the rest of the line. We'll cover the various changes possible with the
ALTER TABLE statement one at a time in this chapter.
This SQL statement starts simply. It's the specifics of the changes that can make it confus-
ing. Actually, that isn't always the reason for the confusion. The reason many developers
have trouble with the ALTER TABLE statement is because they most likely don't use it of-
ten. When you need to make a change to a table, you will probably look in a book or in the
documentation to see how to make a change, enter it on your server, and then forget what
you did. In contrast, because you will frequently use the SQL statements for entering and
retrieving data (i.e., INSERT and SELECT ), their syntax will be easier to remember. So it's
natural that database developers don't always remember how to make some of the changes
possible with the ALTER TABLE statement.
One of the most common alterations you willneed to make to a table is adding a column.
To do this, include the ADD COLUMN clause as the changes at the end of the syntax
shown earlier. As an example of this clause, let's add a column to the bird_families
table to be able to join it to the bird_orders table. You should have created these two
tables in Chapter4 . We'll name the column order_id , the same as in the
bird_orders table. It's acceptable and perhaps beneficial for it to have the same name
as the related column in the bird_orders table. To do this, enter the following from the
mysql client:
ALTER TABLE bird_families
ADD COLUMN order_id INT ;
This is pretty simple. It adds a column to the table with the name order_id . It will con-
tain integers, but it will not increment automatically like its counterpart in the