Modify Tables

Back to Course Website

Using Alter Table Queries

When you create a table with the CREATE TABLE command in MySQL, you specify the column definitions and other options. If you later decide to change any aspect of the table's definition, you can do so using the ALTER TABLE command. This command allows you to change column names, column types, and other aspects of a table's definition.

SYNTAX of ALTER TABLE

ALTER TABLE tableName
        ADD COLUMN columnName dataType ;

Example of ALTER TABLE

ALTER TABLE address_table
         ADD COLUMN lastvisit DATE;

Example of ALTER TABLE that does two things

ALTER TABLE address_table
         ADD COLUMN lastvisit DATE,
         DROP COLUMN postal;

When you use most variations of the ALTER TABLE command, the MySQL server actually performs the alterations in several steps. It first creates a new table with a copy of the existing table's data. Next, the changes you specified in your query are made to the new table. Finally the original table is deleted and the new one is renamed to the old name.

Clients are able to read data from the table during the alteration process, but no data can be written to the table until the process is completed. Because alterations may take a while on large tables and consume a large amount of the server's CPU and memory resources, it is best to alter tables when few clients are using them.

Because ALTER TABLE copies the table, you can also use it to sort a table's data. To do this, use the ORDER BY keywords:

SYNTAX of ORDER BY

ALTER TABLE tableName
        ORDER BY columnName;

Example of ORDER BY

ALTER TABLE address_table
        ORDER BY name;

While you usually do not need to manually sort a table in this way, it can improve performance with a large table that will not be modified frequently. The sorting process can take a long time on a large table.

Add a Column to a Table

Use the ADD COLUMN command to add a column to the table. Specify the new column name, the column type, and any attributes. You can use the same syntax as when you create a table. The basic query syntax specifies the table, the name of the new column, and its column types:

SYNTAX of ADD COLUMN

ALTER TABLE tableName
        ADD COLUMN columnName dataType;

Example of ADD COLUMN

ALTER TABLE address_table
        ADD COLUMN lastvisit DATE;

You can optionally specify the keyword FIRST after ADD COLUMN to add the column at the beginning of the table, or the AFTER keyword and a column name to add it after an existing column . If you do not specify either of these, the column is added as the last column of the table.

SYNTAX of COLUMN FIRST

ALTER TABLE tableName
        ADD COLUMN columnName dataType FIRST;

SYNTAX of COLUMN AFTER

ALTER TABLE tableName
        ADD COLUMN columnName dataType AFTER existingColumnName;

Example of COLUMN AFTER

ALTER TABLE address_table
        ADD COLUMN lastvisit DATE AFTER state;

Make sure that the new column's name does not conflict with other columns in the table. The column must also be compatible with the existing columns: In particular, if there are existing variable-length text columns, such as VARCHAR or TEXT, you cannot add a fixed-length CHAR column.

You can add multiple columns within the same ALTER TABLE command by separating them with commas and enclosing the entire list of new columns in parentheses. For example, the following command adds three new columns to the address table:

SYNTAX of ADD MULTIPLE COLUMNS

ALTER TABLE address_table
         ADD COLUMN (
         country VARCHAR(10),
         unit VARCHAR(5),
         notes TEXT
         );

ADD INDEX

Use the ADD INDEX command to add an index to the table for an existing column. To use the command, specify an optional index name followed by the column or columns to index in parentheses.

SYNTAX of ADD INDEX

ALTER TABLE tableName
         ADD INDEX myIndexName (existingColumnName);

Example of ADD INDEX

ALTER TABLE address_table
         ADD INDEX post_index (postal);

When you use this command to add an index, the MySQL server immediately begins scanning the table and building the index file.

If you use ADD UNIQUE instead of ADD INDEX, a unique index is created. Before adding a unique index, be sure the existing rows of the table have unique values for the column or columns you plan to index.

You can optionally specify more than one column for the index, separated by commas. In this case, the index will be based on the combination of values in all of the columns you list:

SYNTAX of ADD MULTIPLE-COLUMN INDEX

ALTER TABLE tableName
         ADD INDEX myIndexName (existingColumnName, existingColumnName);

This example adds an index that uses both the state and country columns:

Example of ADD MULTIPLE-COLUMN INDEX

ALTER TABLE address_table
         ADD INDEX location_index (state, country);

Additionally, you don't need the "ALTER TABLE" part if you use the CREATE INDEX command. It can add a regular index, a unique index, or a full-text index to an example column.

To add a simple index with CREATE INDEX, specify a name for the new index, the ON keyword, and the table name. This should be followed by one or more column names in parentheses:

SYNTAX of CREATE INDEX

CREATE INDEX myIndexName ON tableName (existingColumnName);

The following example adds an index on the postal column.

Example of CREATE INDEX

CREATE INDEX postindex ON address_table (postal);

Add a Primary Key or Unique Index

The ADD PRIMARY KEY command adds a primary key. This can only be used if the table does not have an existing primary key. To use this command, specify one or more columns to act as the primary key. Each existing row of the table must have a unique value for the column or columns specified. The column you specify must also have the NOT NULL attribute. You can alter the column to add this attribute if necessary.

SYNTAX of ADD PRIMARY KEY

ALTER TABLE tableName
        ADD PRIMARY KEY (existingColumnNameOrNames);

Example of ADD PRIMARY KEY

ALTER TABLE address_table
        ADD PRIMARY KEY (name, address);

A Unique Index is an index column where no value is ever repeated. To add a unique index, you use the ADD UNIQUE keywords with the ALTER TABLE command.

SYNTAX of ADD UNIQUE

ALTER TABLE tableName
        ADD UNIQUE myKeyName (existingColumnNameOrNames);

This example adds a unique index called key1 to the address table, indexing the address and city columns:

Example of ADD UNIQUE

ALTER TABLE address_table
        ADD UNIQUE key1 (address, city);

Add a TimeStamp Column

Timestamp columns are useful for keeping track of when a row of the table has been updated. Each time you modify or insert a row, the row's timestamp field is automatically set to the current date and time.

In MySQL, you can add a timestamp column to an existing table using the same kind of ALTER TABLE / ADD COLUMN command we've already seen:

SYNTAX of ADD TIMESTAMP :

ALTER TABLE tableName	
        ADD COLUMN columnName TIMESTAMP;

For example, the following command adds a timestamp column called 'updatetime' to the address table:

Example of ADD TIMESTAMP:

ALTER TABLE address_table
        ADD COLUMN updatetime TIMESTAMP;

A timestamp column's display width can be up to 14 digits. When 14 digits are used, the column displays the year, month, date, hour, minute, and second values. For example, a TIMESTAMP(14) column set to January 3rd 2005 at midnight would return the value 20050103000000.

WIDTH FORMAT
14 yyyymmddhhmmss
12 yymmddhhmmss
10 yymmddhhmm
8 yyyymmdd
6 yymmdd

Also note that you can use the timestamp column as an index or primary key if you set a timestamp column before adding data into your database.

Add an Auto-Increment Column

While some tables have an obvious choice for a primary key, such as a social security number, some tables have no value that is guaranteed to be unique for each row. In this case, you can use an auto-increment column as a key. When you specify the AUTO_INCREMENT attribute for a column, each row you add is automatically assigned a new unique numeric value for that column. So the column's fields get values like 1,2, 3, 4, and on up.

As with other column types, you can use the ALTER TABLE to add an auto-increment column to an existing table:

SYNTAX of ADD AUTO_INCREMENT

ALTER TABLE tableName
        ADD COLUMN columnName dataType 
        UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

For example, this command adds a new auto-increment column called linkID to the links table:

Example of ADD AUTO_INCREMENT

ALTER TABLE links_table
        ADD COLUMN linkID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
  • Any column that's an auto_increment column must have the NOT NULL attribute. If you insert a NULL value into an auto-increment column, the next sequence number for the field is inserted instead.
  • Auto-increment columns should also have the UNSIGNED attribute because negative numbers can cause conflicts.
  • Data type must be a numeric type.
  • Also note that you can only have one auto-increment column per table.

When you add an auto-increment column to a table with existing data, the rows of the table are assigned values for the column automatically, counting up from one. This allows you to use a new auto-increment column as a primary key or unique index, because the values are guaranteed to be unique.

When you delete a row from a table with an auto-increment column, the sequence number on that row is usually not re-used. If you delete all of the rows of the table, the auto-increment numbers start over at one.

You can specify the first number to be used when assigning values to an auto-increment column when you create the table with the AUTO_INCREMENT parameter.

RENAME or MOVE a Column

The CHANGE command renames an existing column. To use this command, specify the existing column name and the new name.

SYNTAX of CHANGE COLUMN NAME

ALTER TABLE tableName
          CHANGE oldColumnName newColumnName dataType optionalAttributes;

Example of CHANGE COLUMN NAME

ALTER TABLE links_table
        CHANGE link_column url_col CHAR(20);
  • You MUST specify the new column name, so if the name's not supposed to change, just repeat the column's old name.
  • You MUST also put a data type, even if it's not changing.
  • You MAY also specify any attributes of the column you want to change.

Keep in mind that when you rename a column, any applications that refer to the column by name will need to be modified to use the new name. You will also need to refer to the new name in any further references to the column.

You can use the optional keyword FIRST after the column definition to move the column to the beginning of the table. You can use the AFTER keyword followed by a column name to move the column to a new position after the specified column. Changing the order of columns does not affect existing data but may potentially cause problems with applications that were built to work with the table.

Example of MOVE COLUMN

ALTER TABLE links_table
        CHANGE url_col url_col CHAR(20) AFTER description_column;

Change a Column Type

While it is important to choose each column's type and attributes carefully when creating a table, you can change a column's type using ALTER TABLE. The basic syntax for this is just like renaming a column, using the CHANGE keyword:

SYNTAX of CHANGE COLUMN TYPE

ALTER TABLE tableName
          CHANGE columnName newColumnName dataType optionalAttributes;

For eample, the following command changes the 'description' field in the links table to a CHAR(200) column:

Example of CHANGE COLUMN TYPE

ALTER TABLE links_table
          CHANGE description description CHAR(200) NOT NULL;
  • You MUST specify the new column name, so if the name's not supposed to change, just repeat the column's old name.
  • You MUST also put a data type, even if it's not changing.
  • You MAY also specify any attributes of the column you want to change.

You can alternately use the MODIFY keyword, which allows changing the definition for a column without changing its name. To use the command, specify the column name, the new column type, and any options. As with CHANGE, the data is converted wherever possible to the new format.

Basic MODIFY SYNTAX

ALTER TABLE tableName
          MODIFY columnName dataType optionalAttributes;

Example of MODIFY SYNTAX

ALTER TABLE links
          MODIFY description CHAR(200) NOT NULL;

When you change a column's type, MySQL makes an effort to preserve the data in existing rows as much as possible and convert it to the new type.

As when creating a table or adding a column, the MySQL server may not allow some changes. If the table currently has one or more variable-length fields, you cannot change a column's type to a fixed-length CHAR field and vice versa.

Remember that when you want to make one change to a column in a table, often you will have to make other changes that are required. For example if you add a primary key you cannot forget to make that column not null.

Delete a Column

The DROP COLUMN command enables you to delete an existing table column. Use this command with caution, because it deletes all data stored in that column in existing table rows without asking for confirmation. To use DROP COLUMN, simply specify the column name.

SYNTAX of ADD COLUMN

ALTER TABLE tableName
        DROP COLUMN columnName;

Example of ADD COLUMN

ALTER TABLE address_table
        DROP COLUMN postal;

If you attempt to drop a column and the table only has one column, MySQL will return an error because a table must have at least one column. You can delete the table entirely using the DROP TABLE command.

Delete an Index or Primary Key

The DROP INDEX command deletes an existing index, without removing the columns it is based on. To use this command, specify the index name. The index name is the column name by default, or the name you specified when creating the index.

SYNTAX of DROP INDEX

ALTER TABLE tableName
         DROP INDEX myIndexName;

Example of DROP INDEX

ALTER TABLE address_table
         DROP INDEX postindex;

Because this command requires the index name rather than the column name, you can use the SHOW INDEX command to determine the name of the index if you are not sure. If you did not specify an index name when the index was created, it will have the same name as the column it indexes.

SYNTAX of SHOW INDEX

SHOW INDEX FROM tableName;

The DROP PRIMARY KEY command removes an existing primary key. This only removes the indexing information, not the column or columns that act as the key. This command does not require any parameters.

SYNTAX of DROP PRIMARY KEY

ALTER TABLE tableName
        DROP PRIMARY KEY;

Because there is only one primary key, an index name is not required.

If you are removing an index or primary key, you often need to add a new index or primary key. You can perform both of these actions with a single ALTER TABLE command. The following example removes the index and primary key from the address table and then adds a new auto-increment column and sets it as the new primary key.

Example of drop primary key and index and add primary key:

ALTER TABLE address_table
        DROP PRIMARY KEY,
        DROP INDEX stateindex,
        ADD COLUMN id INT UNSIGNED AUTO_INCREMENT,
        ADD PRIMARY KEY (id);

Rename a Table

The RENAME command renames an existing table. To use this command, specify the existing table name and the new name. You can use RENAME TO as a synonym for RENAME.

SYNTAX of RENAME TABLE

ALTER TABLE oldTableName
        RENAME TO newTableName;

Example of RENAME TABLE

ALTER TABLE temp
        RENAME TO temp2;

Unlike other ALTER TABLE queries, the MySQL server does not create a temporary copy of the table when renaming a table. Instead, the data files for the table in the file system are simply renamed. This is much faster than copying the table, and is unaffected by the amount of data stored in the table.

Another method that is supported is the RENAME TABLE command for the same purpose. The following example renames the MailList table to mail using the RENAME TABLE command:

SYNTAX of RENAME TABLE SYNTAX

RENAME TABLE MailList TO mail;

Change a Table Type

You can change table options using the ALTER TABLE command. These options include TYPE (the table type), COMMENT (the optional comment field), and other options. To change options, specify each option followed by an equal sign and a new value.

SYNTAX of SETTING TABLE OPTION

ALTER TABLE tableName
       optionName = newValue;

Example of SETTING TABLE OPTION

ALTER TABLE address_table
       ENGINE=InnoDB ;

Example of setting two different TABLE OPTIONS

ALTER TABLE address_table
       ENGINE=InnoDB,
       COMMENT = "I changed the table type.";