Altering Existing Table Structures


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.



MySQL Tutorial
MySQL Tutorial
ISBN: 0672325845
EAN: 2147483647
Year: 2003
Pages: 261

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net