ALTER TABLE - CHANGE clauses

These clauses of the MySQL statement, ALTER TABLE are used to alter an existing column in a MySQL table.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

ALTER [IGNORE] TABLE table
ALTER [COLUMN] column {SET DEFAULT value|DROP DEFAULT} |
CHANGE [COLUMN] column column definition [FIRST|AFTER column]
MODIFY [COLUMN] column definition [FIRST|AFTER column]

Explanation

These three clauses of the MySQL statement, ALTER TABLE are used to alter an existing column in a MySQL table. The first syntax structure is used either to set the default value of a column to a particular value or to reset it back to its default value for its column type (usually NULL or 0). The other two syntax structures are used primarily to change the column definitions. The COLUMN keyword is optional and has no effect.

Examples

Here's an example of the first clause of this MySQL statement:

ALTER TABLE clients
ALTER COLUMN city SET DEFAULT 'New Orleans';

This statement sets the default value of the city column in the clients table to a value of New Orleans, because that's where most of the clients are located.

The clauses that change column definitions are roughly synonymous; they follow the standards of different SQL systems for the sake of compatibility (e.g., MODIFY is used with Oracle). They can also be used to relocate the column within the table schema with the FIRST or the AFTER keywords. If a column's data type is changed, MySQL attempts to adjust the data to suit the new data type. If a column width is shortened, MySQL truncates the data and generate warning messages for the affected rows. Indexes related to changed columns will be adjusted automatically for the new lengths.

In the CHANGE clause, the current column name must be specified first, followed by either the same column name if the name is to remain the same, or a new column name if the name is to be changed. The full column definition for the column must be given as well, even if it's not changing.

The MODIFY clause cannot be used to change a column's name, so the column name appears only once with it.

The following SQL statement shows the columns in the clients table where the column name begins with a c and contains an i. After viewing these limited results, we change one column using each of the clauses for changing column definitions.

SHOW COLUMNS FROM clients LIKE 'c%i%';

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| client_id    | int(11)      | NO   | PRI | NULL    | auto_increment | 
| client_name  | varchar(255) | YES  | MUL | NULL    |                | 
| city         | varchar(255) | YES  |     | NULL    |                | 
| client_zip   | char(10)     | YES  |     | NULL    |                | 
| client_state | char(2)      | YES  |     | NULL    |                | 
+--------------+--------------+------+-----+---------+----------------+

ALTER TABLE clients
CHANGE COLUMN city client_city VARCHAR(100) CHARACTER SET 'latin2',
MODIFY COLUMN client_state CHAR(4) AFTER client_city;

After looking at the current columns, we've decided to change the name of the city column to client_city to match the other related columns, and to enlarge the client_state column and move it before the column for the postal zip code. To do this, the CHANGE clause is used to change the name of the city column, but not its column type and size. The second clause changes the column type and size and relocates the client_state column to a position after the client_city column.

When a column is changed, MySQL will attempt to preserve the data. If a column size is reduced, the data won't be completely deleted, but may be truncated, in which case the results will show a number of warnings. Use the SHOW WARNINGS statement to view them.