Working with Tables - Part II

Altering Table Structures

Beside creating table, MySQL allows you to alter existing table structures with a lot of options. Here are the ALTER TABLE statement:

ALTER [IGNORE] TABLE table_name options[, options...]
options:
       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_table_name
 or    ORDER BY col_name
 or    table_options

Most of these option are obvious, we will explain some here:

• The CHANGE and MODIFY are the same, they allow you to change the definition of the column or its position in the table.
• The DROP COLUMN will drop the column of the table permanently, if the table contain data all the data of the column will be lost.
• The DROP PRIMARY KEY and DROP INDEX only remove the primary key or index of the column.
• The DISABLE and ENABLE KEYS turn off and on updating indexes for MyISAM table only.
• The RENAME Clause allows you the change the table name to the new one.

Dropping Tables

To delete table from the database, you can use DROP TABLE statement:

DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name,...]

TEMPORARY keyword is used for dropping temporary tables. MySQL allows you to drop multiple table at once by listing them and separated each by a comma. IF EXISTS is used to prevent you from deleting table which does not exist in the database.

Empty Table's Data

In some cases, you want to delete all table data in a fast way and reset all auto increment columns. MySQL provide you TRUNCATE table statement to do so. The statement is in this form:

TRUNCATE TABLE table_name

There are some points you should remember before using TRUNCATE TABLE statement:

• TRUNCATE TABLE statement drop table and recreate it so it is much faster than DELETE TABLE statement but it is not transaction-safe.
• The number of deleted rows is not return like DELETE TABLE statement.
• ON DELETE triggers are not invoked because TRUNCATE does not use DELETE statement.