Modifying the Structure of Existing Tables

Chapter 7 - Advanced SQL Usage
byGareth Downes-Powellet al.
Wrox Press 2003

Very often you will need to change your application. The database for our hotel application is rather simple. If we want to turn it into a real application we might want to add a description of a room. You may also like to remove a field from a table because you no longer need it. The modification of an existing table is done using the ALTER TABLE command.

While ALTER TABLE is executing, the original table is readable by other clients. Updates and writes to the table are stalled until the new table is ready.

The ALTER TABLE Syntax

The following table will show you the complete syntax of the ALTER TABLE SQL command. The indexes part will be reviewed in the following section.

     ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]     alter_specification:             ADD [COLUMN] create_definition [FIRST | AFTER column_name ]       or    ADD [COLUMN] (create_definition, create_definition,...)       or    ADD INDEX [index_name] (index_col_name, ...)       or    ADD PRIMARY KEY (index_col_name,...)       or    ADD UNIQUE [index_name] (index_col_name, ...)       or    ADD FULLTEXT [index_name] (index_col_name,...)       or    ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)                  [reference_definition]       or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}       or    CHANGE [COLUMN] old_col_name create_definition       or    MODIFY [COLUMN] create_definition       or    DROP [COLUMN] col_name       or    DROP PRIMARY KEY       or    DROP INDEX index_name       or    RENAME [TO] new_tbl_name       or    ORDER BY col or table_options 

IGNORE

The IGNORE expression controls how the ALTER works if there are duplicates on unique keys in the new table. If IGNORE isn't specified, the copy is aborted and rolled back. If IGNORE is specified, then for rows with duplicates on a unique key, only the first row is used; the others are deleted.

ADD

The ADD column expression allows you to create a new column on the existing table. It allows you to specify the position of the new column in the table. The keyword COLUMN is optional, as is the position clause. If not specified MySQL will create the new column at the end of the table.

Example: We want to add the column 'description' for the table room. We want the new column to be placed after the column 'bed'. Here is the SQL command:

     ALTER TABLE room ADD description varchar(255) AFTER bed 

create_definition

In the ADD column expression we have the create_definition expression. This is supposed to be replaced with a column definition. The syntax used is the same as when you create a new table. Refer to Chapter 2 for the syntax of table creation.

ALTER

The ALTER column expression is used only when you want to change the default value for a column. When we added the field 'description' we did not mention any default value. For example, in our web application we would like to have at least the value 'no description yet' instead of a blank empty value. To change the default value of the column 'description' the following SQL command is used:

     ALTER TABLE room ALTER description SET DEFAULT "no description yet" 

CHANGE

The CHANGE expression will allow you to modify the definition of a field without losing all the table's values for this field. We have defined the description field as a varchar of 255 bytes in size. This may look a bit short if you want to have a big description. We will now change it into a text type:

     ALTER TABLE room CHANGE description description text 

Note 

When changing the definition of a field, you must specify first the current name of the field, then Its new name, even if you don't change the name.

If we wanted to change the name of the field 'description' into 'roomdescription', we would use the following SQL command:

     ALTER TABLE room CHANGE description roomdescription text 

DROP

The DROP column expression allows you to remove a column from your table. If we want to remove the column 'description' from the table room we will use:

     ALTER TABLE room DROP description 

RENAME

The RENAME expression allows you rename an existing table. To rename the table 'room' into 'newroom', use the following SQL command:

     ALTER TABLE room RENAME TO newroom