Database Reference
In-Depth Information
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 topic:
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 involv-
ing these steps:
1.
Create a new table with the new column layout.
2.
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.
