SQL Pocket Guide, 2nd 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 (Oracle)

Use ALTER TABLE . . . ADD to add columns and table constraints:

ALTER TABLE oracle_example ADD (
   lower_name VARCHAR2(15),
   CONSTRAINT lower_name
      CHECK (lower_name = LOWER(name))
);

Use MODIFY to change a column’s datatype, default value, or nullability. You may also add new constraints to a column. Anything you do not specify is left unchanged:

ALTER TABLE oracle_example MODIFY (
   name VARCHAR2(30) DEFAULT 'Missing!'
      CONSTRAINT name_canbe_null NULL,
   country DEFAULT NULL,
   indexed_name varchar2(30) NOT NULL
      CONSTRAINT no_leading_space
         CHECK (indexed_name = LTRIM(indexed_name))
);

Use DROP to remove a column or constraint. Each drop must be specified separately, and no parentheses are used:

ALTER TABLE oracle_example
   DROP CONSTRAINT lower_name;

ALTER TABLE oracle_example
   DROP COLUMN lower_name;

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 datatype or default value. You can change only one item at a time, so you ...