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 following example adds a column to a table:
ALTER TABLE vendors ADD vend_phone CHAR(20);
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:
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:
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.