Altering a Table


Tables can be altered to add or remove a column.
To alter a table and add a column, use the following SQL ALTER TABLE syntax:


ALTER TABLE myTable ADD COLUMN column6 varchar(24)

To alter a table and remove a column, use the following SQL ALTER TABLE syntax:


ALTER TABLE myTable DROP COLUMN column3

The ALTER TABLE statement does not support null column values. If a table is ALTERed and a column is added, the column must allow null values since there will likely be existing rows in the table that do not have a value for the added column.

ALTER TABLE can be used to remove column contstraints.
Column constraints cannot be added through the ALTER TABLE syntax, only dropped.
To alter a table and drop a primay key, use the following SQL ALTER TABLE syntax:


ALTER TABLE myTable DROP PRIMARY KEY column4

This will remove the primay key from column4 if there is no other table that has a FOREIGN KEY to the table myTable. Removing a primary key means that the column column4 is allowed to have duplicate values.

The ALTER TABLE statement can be used to remove a UNIQUE column constraint using the following syntax:


ALTER TABLE myTable DROP UNIQUE column3

The ALTER TABLE statement can also be used to remove a FOREIGN KEY from a column using the following syntax:


ALTER TABLE theTable DROP FOREIGN KEY column2
 
 

DBLX SQL Guide