SQL: ALTER TABLE

The ALTER TABLE statement allows to change the structure of a table after it has been created. New columns can be added with the ADD clause. Existing columns may be removed from a table using the Drop clause.

The syntax of the ALTER TABLE statement is as follows:

ALTER TABLE table_name 
ADD column_name data_type

ALTER TABLE table_name 
DROP COLUMN column_name

Note: The ALTER TABLE statement is not part of ANSI/ISO standard. This means that all the commercial dialects of SQL implement different clauses and command syntax.

Example #1

ALTER TABLE Orders 
ADD Quantity SMALLINT 

This example adds a single column to the “Orders” table by specifying the new column name and data type.

Example #2

ALTER TABLE EmployeeAddressTable 
ADD Description VARCHAR(20) DEFAULT 'USA' NOT NULL

This example adds a column named “Description” to the “EmployeeAddressTable” table. We also add the reserved words NOT NULL to require valid data to be added to that column and specify a default value.

Example #3

ALTER TABLE AntiqueOwners DROP COLUMN State

This example drops the “State” column from the “AntiqueOwners” table.