Web Database Applications with PHP and MySQL, 2nd Edition

Manipulating Data and Databases

In this section, we show you how to alter a database's structure after you've created it. We also expand on the topics of creating, inserting, deleting, and updating data, including how to work with external files and multiple tables, and optimizing queries.

Altering Databases

Altering a table is unusual: most of the time, you'll define the structure of a table before you create it and you won't change it during its lifetime. However, indexes, attributes, modifiers, and other features of a table can be changed after creation, and this is sometimes a useful feature when you want to add a new index that supports a new query, modify an attribute type or length when needed, or tune your database.

Adding indexes is a popular use of the ALTER TABLE statement. For example, to add an index to the customer table, you can run:

ALTER TABLE customer ADD INDEX cities (city);

The label cities is the name of the new index and the attribute that's indexed is city.

To remove the same index from the customer table, use:

ALTER TABLE customer DROP INDEX cities;

This removes the index, not the attribute.

The DROP statement discussed in Chapter 5 can also be used to remove an index. For example:

DROP INDEX cities ON customer;

Behind the scenes, MySQL converts this to an ALTER TABLE statement.

The ALTER TABLE statement can also be used to add, remove, and alter all other aspects of the table, such as attributes and the primary index. For example, to add a new fax attribute to the ...