Altering Tables

The ALTER TABLE command allows you to alter tables, fields and indexes of a table.

Renaming a Table

The RENAME modifier allows you to rename a table. The following example renames the table search to site.

mysql> ALTER TABLE search RENAME site;

Adding Columns to a Table

The ADD COLUMN modifier allows you to add a column to a table. The following example adds a field called modified to the search table.

mysql> ALTER TABLE search ADD COLUMN modified TIMESTAMP;

The FIRST and AFTER modifiers may be used to position the field within the table. The following places the new field as the first field in the table.

mysql> ALTER TABLE search ADD COLUMN modified TIMESTAMP FIRST;

The next example could be used to add the new field immediately after the field Keywords.

mysql> ALTER TABLE search ADD COLUMN modified TIMESTAMP AFTER Keywords;

Dropping a Column from a Table

The DROP COLUMN modifier may be used to drop a column from the table. The following example drops the field modified.

mysql> ALTER TABLE search DROP COLUMN modified;

Dropping a Indexes from a Table

The DROP INDEX modifier may be used to drop an index from a table. The following example drops the index keyIndex from the search table.

mysql> ALTER TABLE search DROP INDEX keyIndex;

Modifying columns

When you modify a column, you must specify the attributes for the column again. The following example renames the Description field to PageDescription in the search table.

mysql> ALTER TABLE search CHANGE Description PageDescription VARCHAR(255) NOT NULL;

If you only want to change the attributes, use the same column name as in the following example:

mysql> ALTER TABLE search CHANGE Description Description VARCHAR(200) NOT NULL;

Deleting a Table

Tables are deleted from the database using the DROP statement. The following example drops the table search from the database.

mysql> DROP TABLE search;