ALTER TABLE Statement

In this chapter, we will learn more about adding, modifying, and deleting tables, adding new information to tables or making changes to existing information. Creating new tables is the first step in implementing the database after design. Sometimes, additional tables are added after a database is already in use to accommodate the growth of the organization in new domains. Also, existing tables are sometimes modified due to problem that was not initially apparent during the design stages. We already took a brief look at creating a table using SQL so let us recap this information and move on to altering tables.

CREATE TABLE GymService ( ServiceID int(2) PRIMARY KEY, ServiceName varchar(30), ServiceCost numeric(5,2) )

The statement above creates a GymService table with an integer Primary Key called ServiceID, a series of characters that forms the Service name, and a service cost field that may contain a number that is less than or equal to 999 and have two decimal digits. If the Gym's owner decides to start a new service that would cost $1000, our database would fall short of accommodating this piece of data. So, we would have to alter the table in order for the ServiceCost field to contain an extra digit. The following SQL command would do just this.

ALTER table GymService modify ServiceCost numeric(6,2)

This statement would make the column ServiceCost accommodate numbers up to 9999.99. Now, let us look at a different sort of request. Suppose someone wants to change the name of the column ServiceCost to ServiceFee. The following command will accomplish this:

ALTER table GymService change ServiceCost ServiceFee

We learned that we use the MODIFY clause to make modifications to the type of data that a column holds and in the same way we use the CHANGE clause to change the name of the column itself. Consider another scenario: There is a need to add a new column that contains a description of the service. The ADD clause may be used to add new columns to a database

ALTER table GymService ADD ServiceDesc varchar(100)

We have used the DROP keyword to delete tables in the third chapter. Used differently, the same keyword deletes a column. The following command would delete the new ServiceDesc column:

ALTER table GymService DROP ServiceDesc

As of MS Office 2001, the SQL option is not available for creating, altering or deleting tables in MS Access. Note that there is no way to get to the SQL editor in table design mode. CREATE, ALTER, and DROP may be performed only through the GUI.