PHP & MySQL® Web Development All-in-One Desk Reference for Dummies®

3.3. Changing the Database Structure

Your database isn't written in stone. You can change the name of any table; add, drop, or rename a column in any table; or change the data type or other attributes of any column.

Changing a database is not a rare occurrence. You might want to change your database for many reasons. For example, suppose that you defined the column lastName with VARCHAR(20) in a database that contains the names of all the employees in your company. At the time, 20 characters seemed sufficient for a last name. But you just received a memo announcing the new CEO, John Schwartzheimer-Losertman. Oops. MySQL will truncate his name to the first 20 letters, Schwartzheimer-Loser — a less-than-desirable new name for the boss. So you need to make the column wider — pronto.

3.3.1. Changing the database structure with SQL queries

You can change the database structure with an ALTER query. The basic format for this query is ALTER TABLE tablename, followed by the specified changes. Table 3-7 shows the changes that you can make.

Table 3-7. Changes You Can Make with the ALTER Query

Change	Description
ADD columnname definition	Adds a column; definition includes the data type and optional definitions.
ALTER columnname SET DEFAULT value	Changes the default value for a column.
ALTER columnname DROP DEFAULT	Removes the default value for a column.
CHANGE columnname newcolumnname definition	Changes the definition of a column and renames the column; definition includes the data type and optional ...