SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 298

The SQL ALTER TABLE Statement
The SQL ALTER TABLE statement is an SQL DDL statement that is used to change the
structure of an existing table. It can be used to add, remove, or change columns. It also can be
used to add or remove constraints.
Adding and Dropping Columns
The following statement will add a column named MyColumn to the CUSTOMER table by us-
ing the SQL ADD clause in the SQL ALTER TABLE statement:
/* *** SQL-ALTER-TABLE-CH07-01 *** */
ALTER TABLE CUSTOMER
ADD MyColumn Char(5) NULL;
You can drop an existing column by using the SQL DROP COLUMN clause in the SQL ALTER
TABLE statement:
/* *** SQL-ALTER-TABLE-CH07-02 *** */
ALTER TABLE CUSTOMER
DROP COLUMN MyColumn;
Note the asymmetry in syntax; the keyword COLUMN is used in the DROP COLUMN
clause, but not in the ADD clause. You can also use the ALTER TABLE statement to change
column properties, as you will see in the next three chapters.
Adding and Dropping Constraints
The ALTER TABLE statement can be used with an SQL ADD CONSTRAINT clause to add a
constraint as follows:
/* *** SQL-ALTER-TABLE-CH07-03 *** */
ALTER TABLE CUSTOMER
ADD CONSTRAINT MyConstraint CHECK
(LastName NOT IN ('RobertsNoPay'));
You can also use the ALTER TABLE statement with an SQL DROP CONSTRAINT clause to
DROP a constraint:
/* *** SQL-ALTER-TABLE-CH07-04 *** */
ALTER TABLE CUSTOMER
DROP CONSTRAINT MyConstraint;
By ThE WAy The SQL ALTER TABLE statement can be used to add or drop any of
the SQL constraints. You can use it to create primary keys and alternate
keys, to set null status, to create referential integrity constraints, and to create data
constraints. In fact, another SQL coding style uses CREATE TABLE only to declare
the table's columns; all constraints are added using ALTER TABLE. We do not use that
style in this text, but be aware that it does exist and that your employer might require it.
The SQL DROP TABLE Statement
It is very easy to remove a table in SQL. In fact, it is far too easy. The following SQL DROP
TABLE statement will drop the TRANS table and all of its data:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-DROP-TABLE-CH07-01 *** */
DROP TABLE TRANS;