SQL Pocket Guide, 3rd Edition

Tables, Modifying

You can change the columns and other attributes of a table using the ALTER TABLE statement. The syntax varies significantly among vendors. The following subsections show the same sequence of common table alterations. Many other types of changes are possible; consult your vendor documentation for details.

Modifying a Table: DB2

Use ALTER TABLE’s ADD clause to add a column or table constraint. You may add more than one item at a time:

ALTER TABLE db2_example
   ADD COLUMN lower_name VARCHAR(15)
   ADD CONSTRAINT lower_name
          CHECK(lower_name = LOWER(name));

Use the ALTER clause to change a column’s default value or data type. For example:

ALTER TABLE db2_example
   ALTER COLUMN name SET DEFAULT 'Missing!'
   ALTER COLUMN indexed_name
      SET DATA TYPE VARCHAR(30);

You can change only one item at a time for a given column. If you need to change both name and data type for a given column, you will need to issue separate ALTER TABLE statements for each of those two changes.

You can add table constraints but not column constraints, so the no_leading_space constraint added at the column level on other platforms must be added at the table level for DB2:

ALTER TABLE db2_example
   ADD CONSTRAINT no_leading_space
      CHECK (indexed_name = LTRIM(indexed_name));

DB2 9.7 and higher support changing the nullability of a column. For example:

ALTER TABLE db2_example
   ALTER COLUMN name SET NOT NULL;

To change the nullability of a column prior to DB2 9.7, you must drop and recreate the table. (Remember that columns ...