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
ALTER TABLE tblname ADD COLUMN colname coltype
Adds a new column to the end of the table.
ALTER TABLE tblname CHANGE COLUMN colname
Allows you to change the data type and newcolname newcoltype properties.
ALTER TABLE tblname DROP COLUMN colname
Removes a column from a table, including all of its data.
ALTER TABLE tblname ADD INDEX indexname (columns)
Adds a new index on the listed column(s).
ALTER TABLE tblname DROP INDEX indexname
Removes an existing index.
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:
Access the mysql
client and select the accounting
database, if you have not already.
Rename the contact_name
field (Figure 4.14
ALTER TABLE clients
CHANGE COLUMN contact_name
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.
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.
Create a new contact_last_name
column (Figure 4.15
ALTER TABLE clients
ADD COLUMN contact_last_name VARCHAR(25)
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.
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.
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.