MySQL

Update Tables using ALTER TABLE

ALTER TABLE is used to update the schema ( or definitions) of an existing table.

ALTER TABLE Syntax:

ALTER TABLE tablename
(
    ADD     column          datatype  [NULL|NOT NULL]  [CONSTRAINTS],
    CHANGE  column columns  datatype  [NULL|NOT NULL]  [CONSTRAINTS],
    DROP    column,
    ...
);

You should design tables carefully so extensive changes are not required later on and should not use ALTER TABLE to alter tables when they contain data.

To change a table using ALTER TABLE, as above syntax mentioned that you must specify the following information:

  • The name of the table to be altered after the command ALTER TABLE.

  • The list of changes to be made.

Adding and Removing Column using ALTER TABLE

Use the following example to add a column to a table:

ALTER TABLE categories
ADD categoryname CHAR(20);

This statement adds a column named categoryname to the categories table. You should specify the datatype when adding a column (as we added CHAR).

To remove a column, use the DROP as following example shows:

ALTER TABLE categories
DROP COLUMN categoryname;

This statement removes a column named categoryname from the categories table.

How to Create Foreign Keys using ALTER TABLE

One common use for ALTER TABLE is to define foreign keys.

The purpose of a foreign key constraint is to define a relationship between two tables. Now we are creating relationship between four tables using foreign key constraints. (Note: only innoDB engine supports this feature) The following is the code used to define the foreign keys:

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.

Use ALTER TABLE carefully and be sure you have a complete set of backups (both schema and data) before proceeding. Database table changes cannot be undone and if you drop a column that you do need, you might lose all the data in that column.