Previous Section  < Day Day Up >  Next Section

4.8 ALTER TABLE

After creating a table, you might discover that its structure is not quite right for its intended use. If that happens, you can change the table's structure. One way to do this is to remove the table with DROP TABLE and then issue another CREATE TABLE statement that defines the table correctly. This can be a drastic method: If the table already contains data, dropping and re-creating the table destroys its contents unless you first make a backup. To change a table "in place," use the ALTER TABLE statement. With ALTER TABLE, you can modify a table's structure in the following ways:

  • Add or drop columns

  • Change the name or definition of a column

  • Add or drop indexes

  • Sort the table's rows in a particular order

  • Rename the table

This section describes how to perform all the possible changes except for adding and dropping indexes. Adding and dropping of indexes is covered in a later section that focuses specifically on indexing issues. (See section 4.9, "Creating and Dropping Indexes.")

Most of the examples shown in this section use a table named HeadOfState, designed to keep track of world leaders. Assume that the table initially has the following structure:







CREATE TABLE HeadOfState

(

    ID          INT NOT NULL,

    LastName    CHAR(30) NOT NULL,

    FirstName   CHAR(30) NOT NULL,

    CountryCode CHAR(3) NOT NULL,

);

The corresponding DESCRIBE output for the table is as follows:







mysql> DESCRIBE HeadOfState;

+-------------+----------+------+-----+---------+-------+

| Field       | Type     | Null | Key | Default | Extra |

+-------------+----------+------+-----+---------+-------+

| ID          | int(11)  |      |     | 0       |       |

| LastName    | char(30) |      |     |         |       |

| FirstName   | char(30) |      |     |         |       |

| CountryCode | char(3)  |      |     |         |       |

+-------------+----------+------+-----+---------+-------+

4.8.1 Adding and Dropping Columns

To add a new column to a table, use ALTER TABLE with an ADD clause that specifies the column's definition. A column definition uses the same syntax for ALTER TABLE as for CREATE TABLE. For example, to add a DATE column named Inauguration to record the date the leaders listed in the table assumed office, you can issue this statement:







ALTER TABLE HeadOfState ADD Inauguration DATE NOT NULL;

This ALTER TABLE changes the table structure as follows:







mysql> DESCRIBE HeadOfState;

+--------------+----------+------+-----+------------+-------+

| Field        | Type     | Null | Key | Default    | Extra |

+--------------+----------+------+-----+------------+-------+

| ID           | int(11)  |      |     | 0          |       |

| LastName     | char(30) |      |     |            |       |

| FirstName    | char(30) |      |     |            |       |

| CountryCode  | char(3)  |      |     |            |       |

| Inauguration | date     |      |     | 0000-00-00 |       |

+--------------+----------+------+-----+------------+-------+

As shown in the DESCRIBE output, when you add a new column to a table, MySQL places it after all existing columns. This is the default placement unless you specify otherwise. To indicate that MySQL should place the new column in a specific position within the table, append either the keyword FIRST or the keyword-identifier combination AFTER column_name to the column definition. For example, assume that you had executed this ALTER TABLE statement instead of the previous one:







ALTER TABLE HeadOfState ADD Inauguration DATE NOT NULL FIRST;

The FIRST keyword tells ALTER TABLE to place the new column before all existing columns (in the "first" position), resulting in the following table structure:







mysql> DESCRIBE HeadOfState;

+--------------+----------+------+-----+------------+-------+

| Field        | Type     | Null | Key | Default    | Extra |

+--------------+----------+------+-----+------------+-------+

| Inauguration | date     |      |     | 0000-00-00 |       |

| ID           | int(11)  |      |     | 0          |       |

| LastName     | char(30) |      |     |            |       |

| FirstName    | char(30) |      |     |            |       |

| CountryCode  | char(3)  |      |     |            |       |

+--------------+----------+------+-----+------------+-------+

Using AFTER column_name tells ALTER TABLE to place the new column after a specific existing column. For example, to place the new Inauguration column after the existing FirstName column, you would issue this statement:







ALTER TABLE HeadOfState ADD Inauguration DATE NOT NULL AFTER FirstName;

This ALTER TABLE statement would result in a table structure that looks like this:







mysql> DESCRIBE HeadOfState;

+--------------+----------+------+-----+------------+-------+

| Field        | Type     | Null | Key | Default    | Extra |

+--------------+----------+------+-----+------------+-------+

| ID           | int(11)  |      |     | 0          |       |

| LastName     | char(30) |      |     |            |       |

| FirstName    | char(30) |      |     |            |       |

| Inauguration | date     |      |     | 0000-00-00 |       |

| CountryCode  | char(3)  |      |     |            |       |

+--------------+----------+------+-----+------------+-------+

You cannot add a column with the same name as one that already exists in the table; column names within a table must be unique. Column names are not case sensitive, so if the table already contains a column named ID, you cannot add a new column using any of these names: ID, id, Id, or iD. They all are considered to be the same name.

To drop a column, use a DROP clause. In this case, it's necessary only to name the column you want to drop:







ALTER TABLE table_name DROP column_name;

4.8.2 Modifying Existing Columns

There are two ways to change the definition of an existing column within a table. One of these also enables you to rename the column.

The first way to alter a column definition is to use a MODIFY clause. You must specify the name of the column that you want to change, followed by its new definition. Assume that you want to change the ID column's datatype from INT to BIGINT, to allow the table to accommodate larger identification numbers. You also want to make the column UNSIGNED to disallow negative values. The following statement accomplishes this task:







ALTER TABLE HeadOfState MODIFY ID BIGINT UNSIGNED NOT NULL;

DESCRIBE now shows the table structure to be as follows:







mysql> DESCRIBE HeadOfState;

+--------------+---------------------+------+-----+------------+-------+

| Field        | Type                | Null | Key | Default    | Extra |

+--------------+---------------------+------+-----+------------+-------+

| ID           | bigint(20) unsigned |      |     | 0          |       |

| LastName     | char(30)            |      |     |            |       |

| FirstName    | char(30)            |      |     |            |       |

| Inauguration | date                |      |     | 0000-00-00 |       |

| CountryCode  | char(3)             |      |     |            |       |

+--------------+---------------------+------+-----+------------+-------

Note that if you want to disallow NULL in the column, the column definition provided for MODIFY must include the NOT NULL option, even if the column was originally defined with NOT NULL. This is true for other column options as well; if you don't specify them explicitly, the new definition won't carry them over from the old definition.

The second way to alter a column definition is to use a CHANGE clause. CHANGE enables you to modify both the column's definition and its name. To use this clause, specify the CHANGE keyword, followed by the column's existing name, its new name, and its new definition, in that order. Note that this means you must specify the existing name twice if you want to change only the column definition (and not the name). For example, to change the LastName column from CHAR(30) to CHAR(40) without renaming the column, you'd do this:







ALTER TABLE HeadOfState CHANGE LastName LastName CHAR(40) NOT NULL;

To change the name as well (for example, to Surname), provide the new name following the existing name:







ALTER TABLE HeadOfState CHANGE LastName Surname CHAR(40) NOT NULL;

4.8.3 Renaming a Table

Renaming a table changes neither a table's structure nor its contents. The following statement renames table t1 to t2:







ALTER TABLE t1 RENAME TO t2;

Another way to rename a table is by using the RENAME TABLE statement:







RENAME TABLE t1 TO t2;

RENAME TABLE has an advantage over ALTER TABLE in that it can perform multiple table renaming operations in a single statement. One use for this feature is to swap the names of two tables:







RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2;

4.8.4 Specifying Multiple Alterations

You can specify multiple alterations for a table with a single ALTER TABLE statement. Just separate the actions by commas:







ALTER TABLE HeadOfState RENAME TO CountryLeader,

    MODIFY ID BIGINT UNSIGNED NOT NULL,

    ORDER BY LastName, FirstName;

    Previous Section  < Day Day Up >  Next Section