Updating Tables

To update table definitions, the ALTER TABLE statement is used. But, ideally, tables should never be altered after they contain data. You should spend sufficient time anticipating future needs during the table design process so extensive changes are not required later on.

To change a table using ALTER TABLE, you must specify the following information:

• The name of the table to be altered after the keywords ALTER TABLE. (The table must exist or an error will be generated.)

• The list of changes to be made.

The following example adds a column to a table:

• Input

ALTER TABLE vendors ADD vend_phone CHAR(20);

• Analysis

This statement adds a column named vend_phone to the vendors table. The datatype must be specified.

To remove this newly added column, you can do the following:

• Input

ALTER TABLE Vendors DROP COLUMN vend_phone;

One common use for ALTER TABLE is to define foreign keys. The following is the code used to define the foreign keys used by the tables in this book:

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num); ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id); ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id); ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

Here four ALTER TABLE statements are used, as four different tables are being altered. To make multiple alterations to a single table, a single ALTER TABLE statement could be used with each of the alterations specified comma delimited.

Complex table structure changes usually require a manual move process involving these steps:

• Create a new table with the new column layout.
  
  
• Use the INSERT SELECT statement (see Chapter 19, "Inserting Data," for details of this statement) to copy the data from the old table to the new table. Use conversion functions and calculated fields, if needed.
  
  
• Verify that the new table contains the desired data.
  
  
• Rename the old table (or delete it, if you are really brave).
  
  
• Rename the new table with the name previously used by the old table.
  
  
• Re-create any triggers, stored procedures, indexes, and foreign keys as needed.
  
  

Caution

Use ALTER TABLE Carefully Use ALTER TABLE with extreme caution, and be sure you have a complete set of backups (both schema and data) before proceeding. Database table changes cannot be undoneand if you add columns you don't need, you might not be able to remove them. Similarly, if you drop a column that you do need, you might lose all the data in that column.