Altering Tables - Learning MySQL and MariaDB - page 89

ment, in the exercises at the end of the chapter. For now, let's remove the column de-
scription from the birds table by entering this from mysql :
ALTER TABLE birds
DROP COLUMN description ;
This will delete the column and the data in that column. So be careful using it. This clause
will be coveredin more depth in Chapter6 .
Renaming a Table
Earlier sectionscovered how to make changes to the columns in a table. This included re-
naming columns. Sometimes, though, you may want to rename a table. You may do this
for style reasons or to change the name of a table to something more explanatory. You
may do it as a method of replacing an existing table, by deleting the existing table first
and then renaming the replacement table to the deleted table's name. This is the situation
in some of the examples in the previous section.
We created a copy of the birds table that we called birds_new in the test database.
Our plan was to modify the birds_new table, then to delete the birds table from the
rookery database and replace it with birds_new table from the test database. To
fully replace the birds table, in this case, we will rename birds_new to birds . This
is not done through the ALTER TABLE statement. That's used only for altering the struc-
ture of columns in a table, not for renaming a table. Instead, we will usethe RENAME
TABLE statement. Let's wait before doing that. For now, a generic example follows of
how you would rename a table. Do not enter this statement, though:
RENAME TABLE table1_altered
TO table1 ;
This SQL statement would rename the table1_altered table to table1 . This as-
sumes that a table named table1 doesn't already exist in the database. If it does, it
won't overwrite that table. Instead, you'll get an error message and the table won't be re-
named.
The RENAME TABLE statement can also be used to move atable to another database.
This can be useful when you have a table that you've created in one database, as we did in
the test database, and now want to relocate it to a different database. Because you can
both rename and relocate a table in the same RENAME TABLE statement, let's do that
with our example instead of using the previous syntax. (Incidentally, relocating a table
without renaming it is also allowed. You would give the name of the new database, with
the same table name.) In our examples, we will have to either delete or rename the un-