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:
01 |
ALTER [IGNORE] TABLE table_name options[, options...] |
03 |
ADD [COLUMN] create_definition [FIRST | AFTER col_name ] |
04 |
or ADD [COLUMN] (create_definition, create_definition,...) |
05 |
or ADD INDEX [index_name] (index_col_name,...) |
06 |
or ADD PRIMARY KEY (index_col_name,...) |
07 |
or ADD UNIQUE [index_name] (index_col_name,...) |
08 |
or ADD FULLTEXT [index_name] (index_col_name,...) |
09 |
or ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...) |
10 |
[reference_definition] |
11 |
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} |
12 |
or CHANGE [COLUMN] old_col_name create_definition |
13 |
[FIRST | AFTER column_name] |
14 |
or MODIFY [COLUMN] create_definition [FIRST | AFTER col_name] |
15 |
or DROP [COLUMN] col_name |
17 |
or DROP INDEX index_name |
20 |
or RENAME [TO] new_table_name |
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:
1 |
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:
1 |
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 posts:
- Changing Table Structure Using MySQL ALTER TABLE
- Managing Database Index in MySQL
- Working with Database Table – Part I
- Managing Databases in MySQL
