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 SyntaxThe 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
IGNOREThe 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.
ADDThe 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_definitionIn 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.
ALTERThe 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"
CHANGEThe 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
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
DROPThe 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
RENAMEThe 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 |