Chapter 8. Modifying Tables with ALTER TABLE
Introduction
You’ll probably find
it necessary on occasion to redesign some of your tables. A change in
an application’s specification may require that you
record information not accounted for in the original definition of a
table used by that application. Or you may find that an
AUTO_INCREMENT column is running out of room to
generate new sequence numbers and you need to change the column to
use a larger integer type. MySQL offers many possibilities for
modifying a table’s structure. This chapter
describes how to make the following types of changes:
Dropping, adding, or repositioning a column.
Columns that have become unnecessary or that you discover to be redundant may be removed to simplify a table and to save space. Or you may move columns from one table to another as part of a normalization procedure. Columns may be added when you need to record additional types of information.
Changing a column definition or name.
If a column as originally created does not serve your purposes, you may be able to correct the problem by redefining it. For example, you can convert a string column that is case sensitive to one that is not, or vice versa. Or you may have an
AUTO_INCREMENTcolumn that is aTINYINTand has room only for 127 sequence values. By changing the column to be unsigned or to use a larger integer type, you can extend the range of the sequence. Renaming a column can be useful if after an upgrade to a more recent version of MySQL you ...