Modifying Tables

The final topic to discuss in this chapter is how to modify an existing table. You might need to do so for any reason, but keep in mind all the rules about normalization, indexes, naming conventions, and the like before you make changes. It's easy to undermine all of the planning you put into a database by making a "quick fix."

The ALTER SQL keyword is primarily used to modify the structure of a table in your database. Commonly this refers to adding, deleting, or changing the columns therein. It also applies to renaming the table as a whole and altering the indexes. The basic syntax of ALTER is:

ALTER TABLE tablename CLAUSE

Because there are so many possible clauses, I've listed the common ones in Table 4.8.

A more complete listing is included in Appendix B, "SQL and MySQL References."

Table 4.8. The ALTER SQL command can be used to modify tables in numerous ways.

Alter Table Clauses
Clause	Usage	Meaning
ADD COLUMN	ALTER TABLE tblname ADD COLUMN colname coltype	Adds a new column to the end of the table.
CHANGE COLUMN	ALTER TABLE tblname CHANGE COLUMN colname	Allows you to change the data type and newcolname newcoltype properties.
DROP COLUMN	ALTER TABLE tblname DROP COLUMN colname	Removes a column from a table, including all of its data.
ADD INDEX	ALTER TABLE tblname ADD INDEX indexname (columns)	Adds a new index on the listed column(s).
DROP INDEX	ALTER TABLE tblname DROP INDEX indexname	Removes an existing index.
RENAME AS	ALTER TABLE tblname RENAME AS newtblname	Changes the name of a table.

To demonstrate using the ALTER command, I'll modify the clients table to separate the contact_name field into the more normalized contact_first_name and contact_last_name columns. In this example, I'm assuming that there's no data in the table yet. If there was, I would need to account for that (perhaps by adding the new columns, moving the data over, and then deleting the original column). Because an ALTER command could have serious repercussions on a table, you should always back up the table before execution (see Chapter 13).

To alter a table's structure:

1.	Access the mysql client and select the accounting database, if you have not already. USE accounting;
2.	Rename the contact_name field (Figure 4.14). ALTER TABLE clients CHANGE COLUMN contact_name contact_first_name VARCHAR(15); This command merely changes the name and data type definition of the contact_name column. Instead of being a VARCHAR(40), the column is now called contact_first_name and is a VARCHAR(15). If any data was in this column, it would remain but be truncated to 15 characters long. Figure 4.14. To rename or redefine a column, use the ALTER TABLE tablename CHANGE COLUMN syntax. Deleting Tables and Databases In order to delete a table or database, you use the DROP command. This is as simple as DROP DATABASE dbname DROP TABLE tablename Obviously once you've deleted a table, all of that table's data is gone for good. Once you've deleted a database, all of its tables and data are history.
3.	Create a new contact_last_name column (Figure 4.15). ALTER TABLE clients ADD COLUMN contact_last_name VARCHAR(25) AFTER contact_first_name; Now the table contains a new column, although there are currently no values in it. When adding a new column to a table, you can use the AFTER columname description to indicate where in the table the new column should be placed. Figure 4.15. To add a new column to a table, use ALTER TABLE tablename ADD COLUMN.
4.	Confirm the table's structure (Figure 4.16). SHOW COLUMNS FROM clients; Figure 4.16. Confirm the table's structure by running a SHOW COLUMNS command.

Tips

• To change the type of an existing tablewhich is perfectly acceptableuse an ALTER command:

  ALTER TABLE tablename ENGINE = MYISAM

• You can also confirm the structure of a table after making alterations using SHOW CREATE TABLE tablename. As you would see from running this query, it would not show the original CREATE statement but rather what CREATE statement would need to be executed in order to recreate the table as it currently is.