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
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
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
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);
ALTER TABLE statements are used, as four different tables are being altered.
To make multiple alterations to a single table, a single
statement could be used with each of the alterations specified comma delimited.
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.