As well as creating and deleting tables, we often need to be able to change the structure of an existing table. We can do this with the ALTER TABLE statement. ALTER TABLE has many, many variations we can use to alter table structure. For example, we could have created the name index on employee as follows : alter table employee add index name (name); Because the ALTER TABLE statement is very flexible, it has many, many possible clauses. The general form of the statement from the MySQL manual is as follows: ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...] alter_spec : ADD [COLUMN] create_definition [FIRST AFTER col_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 [FIRST AFTER column_name ] or MODIFY [COLUMN] create_definition [FIRST AFTER col_name ] or DROP [COLUMN] col_name or DROP PRIMARY KEY or DROP INDEX index_name or DISABLE KEYS or ENABLE KEYS or RENAME [TO] new_tbl_name or ORDER BY col_name or table_options Most of these options are pretty self-evident or correspond to clauses in the CREATE TABLE statement, such as ADD PRIMARY KEY . We will briefly discuss the ones that may not be obvious. The CHANGE and MODIFY clauses are the same: They allow you to change the definition of a column or its position in the table. DROP COLUMN deletes a column from the table, whereas DROP PRIMARY KEY and DROP INDEX delete just the associated index for that column. The DISABLE KEYS clause tells MySQL to stop updating indexes for a MyISAM table only. ENABLE KEYS turns index updating back on. The RENAME clause lets you change the name of a table. The ORDER BY clause will put the rows in the newly altered table in a particular order, like the ORDER BY clause in a SELECT statement, which we will look at in Chapter 6, "Querying MySQL." This order will not be maintained as the data in the table changes over time. The table_options option lets you specify the same table options as at the end of the CREATE TABLE statement ”see earlier in this chapter for details. |