MySQL Cookbook

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_INCREMENT column that is a TINYINT and 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 ...