» Basic MySQL Tutorial » Working with Tables - Part II

Working with Tables - Part II

Summary: following with the previous tutorial on how to create database tables in MySQL, in this tutorial, you will learn how to modify and remove existing database tables.

Altering Table Structures

Beside creating table, MySQL allows you to alter existing table structures with a lot of options.To modify existing database table structure you use the ALTER TABLE statement. The following illustrates the ALTER TABLE statement syntax:

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 options 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.

Deleting 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 deleting temporary tables. MySQL allows you to drop multiple tables 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 also provides you SQL TRUNCATE table statement to allow you to do so. The SQL TRUNCATE statement is as follows:

TRUNCATE TABLE table_name

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

  • TRUNCATE TABLE statement drop table and recreate it therefore it is much faster than DELETE TABLE statement. However it is not transaction-safe.
  • The number of deleted rows is not returned like SQL DELETE TABLE statement.
  • ON DELETE triggers are not invoked because TRUNCATE does not use DELETE statement.
Related Tutorials
Search