Updating and Deleting MySQL Tables

Previous	Table of Contents	Next
Creating Databases and Tables Using SQL Commands		Inserting Data into a MySQL Database

eBookFrenzy.com

Purchase and download the full PDF and ePub versions of this MySQL eBook for only $9.99

Once a table has been created it is inadvisable to modify it once it contains data. Sometimes, however, it is necessary to make a change and such changes can be made using the SQL statements. It is also possible to delete an existing table from a database. In this chapter both of these topics will be covered.

Altering a MySQL Table

A pre-existing table in a database may be modified using the SQL ALTER TABLE statement. The ALTER TABLE statement allows the structure of a table to be modified. Permitted changes include the addition or removal of columns, changing the data type of a column and renaming of the table or columns.

MySQL typically performs an alteration by copying the table to a temporary table, making changes to the temporary table and then deleting the original table and renaming the temporary table to the replace it.

The CREATE TABLE syntax requires the name of the table to be altered, followed by the list of changes that are to be made to the table:

ALTER TABLE table_name alteration_spec, alteration_spec, ....

Adding and Deleting Table Columns

The following example adds a column named product_description to a table named products:

ALTER TABLE products ADD product_description CHAR(20);

To specify the new column is to be the first column in a table:

ALTER TABLE products ADD product_description CHAR(20) FIRST;

To add a new column so that appears after a specific column:

ALTER TABLE products ADD product_description CHAR(20) AFTER product_name;

A column can be removed from a table using the DROP COLUMN keywords together with the name of the columns to be removed. For example:

ALTER TABLE products DROP COLUMN product_description;

Renaming Tables and Columns

Both the name of a table and the names of columns within a table may be renamed.

To rename a table:

RENAME TABLE products TO new_products;

Alternatively this can be achieved using the ALTER TABLE statement:

ALTER TABLE customer RENAME customer2;

The renaming of a column can be achieved using the CHANGE keyword together with the old and new column names followed by the current datatype of the column. For example to change a column of type INTEGER named customer_id to new_customer_id:

ALTER TABLE customer CHANGE customer_name new_customer_name INTEGER;

Changing the Data Type of a Column

The current data type of a column may be changed using the ALTER TABLE statement in conjunction with the MODIFY keyword. The following example changes the data type of a column named prod_id from INT to TINYINT:

ALTER TABLE products MODIFY prod_id TINYINT;

Similarly, the following command increases the length of a column named prod_description to 20 characters:

ALTER TABLE products MODIFY prod_description CHAR(20);

Deleting a Database Table

A table may be deleted from a database using the DROP TABLE statement. All that is required with this statement is the name of the table to be deleted. The deletion is permanent and cannot be undone:

DROP TABLE customers;